Aidez-moi à remanier ce monstre de requête.

Question

C'est un énorme monstre, il va dans un SP donc les variables sont utilisables :

SELECT OwnerName, SUM(AmountPaid) AS Paid, SUM(AmountOwedComplete) AS Owed, SUM(AmountOwedThisMonth) AS OwedMonth,
    SUM(PaidForPast) AS PaidPast, SUM(PaidForPresent) AS PaidPresent, SUM((AmountPaid - PaidForPast - PaidForPresent)) AS PaidFuture, [Description] FROM (
    SELECT OwnerName, AmountPaid, AmountOwedComplete, AmountOwedThisMonth, PaidForPast, [Description],
        (SELECT CASE WHEN (AmountPaid - PaidForPast) < ABS(AmountOwedThisMonth) THEN AmountPaid - PaidForPast
            ELSE ABS(AmountOwedThisMonth) END) AS PaidForPresent
    FROM (
        SELECT OwnerName, AmountPaid, AmountOwedTotal - AmountPaid AS AmountOwedComplete,
            AmountOwedThisMonth, 
            (SELECT CASE WHEN (AmountPaid < ABS((AmountOwedTotal - AmountPaid)) + AmountOwedThisMonth)
                THEN AmountPaid ELSE ABS((AmountOwedTotal - AmountPaid)) + AmountOwedThisMonth END) AS PaidForPast, 
            Description, TransactionDate
         FROM (
            SELECT DISTINCT t.TenantName, p.PropertyName, ISNULL(p.OwnerName, 'Uknown') AS OwnerName, (
                SELECT SUM(Amount) FROM tblTransaction WHERE 
                    Amount > 0 AND TransactionDate >= @StartDate AND TransactionDate <= @EndDate
                    AND TenantID = t.ID AND TransactionCode = trans.TransactionCode
            ) AS AmountPaid, (
                SELECT SUM(Amount) FROM tblTransaction WHERE 
                    tblTransaction.TransactionCode = trans.TransactionCode AND tblTransaction.TenantID = t.ID
            )  AS AmountOwedTotal, (
                SELECT SUM(Amount) FROM tblTransaction WHERE  tblTransaction.TransactionCode = trans.TransactionCode AND tblTransaction.TenantID = t.ID
                    AND Amount < 0 AND TransactionDate >= @StartDate AND TransactionDate <= @EndDate
            ) AS AmountOwedThisMonth, code.Description, trans.TransactionDate FROM tblTransaction trans 
            LEFT JOIN tblTenantTransCode code ON code.ID = trans.TransactionCode
            LEFT JOIN tblTenant t ON t.ID = trans.TenantID
            LEFT JOIN tblProperty p ON t.PropertyID  = p.ID
            WHERE trans.TransactionDate >= @StartDate AND trans.TransactionDate <= @EndDate AND trans.Amount > 0
        ) q
    ) q2
)q3
GROUP BY OwnerName, Description

Voici ce qu'il fait. Il visite tous les locataires et obtient ce qu'ils ont payé ce mois-ci, et tout ce qu'ils doivent. Il calcule ensuite ce qui est payé pour les charges précédentes, ce mois-ci et les charges futures. Il les additionne ensuite en fonction de la description de la charge et du nom du propriétaire.

Cela a l'air horrible, et je veux savoir s'il y a des raccourcis que je manque et qui pourraient être utilisés.

Answer 1

Le tueur ici est le PaidForPast (et dérivés PaidForPresent ), qui semble vouloir indiquer si un locataire a payé son solde complet ou non (si je comprends bien - ce n'est pas facile). Ce calcul doit littéralement être effectué pour chaque transaction de paiement et dépend d'un autre agrégat dérivé de l'historique complet du locataire, ce qui garantit une opération O(n^2).

C'est une chose cruelle, cruelle, à faire à votre base de données, et même si je suis capable de rendre votre requête plus jolie, il n'y a aucun moyen pour vous d'échapper au problème de performance tant que vous êtes forcé de produire ces informations sur la base de l'ensemble spécifique de données disponibles que cette requête implique.

Ce que vous avez ici n'est pas un problème de refactoring/optimisation mais un sérieux problème de conception. En fait, vous avez deux problèmes. Le plus petit problème est que vous encodez la logique métier dans votre couche de données ; le plus gros problème est que le système n'a pas été conçu pour garder la trace de toutes les informations dont il a réellement besoin.

En gros, tous Les informations que vous générez dans cette requête doivent être conservées dans une sorte de tableau d'historique des comptes clients qui enregistre ces agrégats/statistiques pour chaque transaction. Ce tableau peut être maintenu par l'application elle-même ou par un déclencheur sur votre site Web. tblTransaction table.

Ce n'est probablement pas la réponse que vous cherchez, mais j'étais à peu près à mi-chemin de la refactorisation lorsque j'ai réalisé qu'il n'était pas possible de supprimer l'attribut PaidForXYZ nidification.

En fait, la façon la plus rapide de produire ces résultats sera probablement avec un curseur, parce que vous pouvez alors travailler avec les agrégats partiels et les transformer en une opération O(n). B

---

Mise à jour :

Si vous ne vous souciez pas vraiment des performances et que vous voulez simplement nettoyer le système pour qu'il soit plus facile à lire/maintenir, voici ce que j'ai pu trouver de mieux en utilisant quelques CTE :

;WITH Transactions_CTE AS
(
    SELECT
        TenantID,
        TransactionCode,
        Amount,
        CASE
            WHEN Amount > 0 AND TransactionDate BETWEEN @BeginDate AND @EndDate
                THEN Amount
            ELSE 0
        END AS AmountPaid,
        CASE
            WHEN Amount < 0 AND TransactionDate BETWEEN @BeginDate AND @EndDate
                THEN Amount
            ELSE 0
        END AS AmountOwed,
    FROM tblTransaction
),
Summary_CTE AS
(
    SELECT
        t.PropertyID,
        tr.TransactionCode,
        SUM(tr.Amount) AS CumulativeBalance,
        SUM(tr.AmountPaid) AS CurrentPaid,
        SUM(tr.AmountOwed) AS CurrentOwed
    FROM Transactions_CTE tr
    INNER JOIN tblTenant t ON tr.TenantID = t.ID
    GROUP BY t.PropertyID, tr.TransactionCode
),
Past_CTE AS
(
    SELECT
        PropertyID, TransactionCode,
        CumulativeBalance, CurrentPaid, CurrentOwed,
        CASE
            WHEN CurrentPaid < 
              ABS(CumulativeBalance - CurrentPaid) + CurrentOwed
            THEN CurrentPaid
            ELSE ABS(CumulativeBalance - CurrentPaid) + CurrentOwed
        END AS PaidForPast
    FROM Summary_CTE
),
Present_CTE AS
(
    SELECT
        PropertyID, TransactionCode,
        CumulativeBalance, CurrentPaid, CurrentOwed,
        PaidForPast,
        CASE
            WHEN (CurrentPaid - PaidForPast) < ABS(CurrentOwed)
            THEN CurrentPaid - PaidForPast
            ELSE ABS(CurrentOwed)
        END AS PaidForPresent
     FROM Past_CTE
)
SELECT
    ISNULL(p.OwnerName, 'UNKNOWN') AS OwnerName,
    c.[Description],
    CumulativeBalance, CurrentPaid, CurrentOwed,
    CumulativeBalance - CurrentPaid AS CumulativeOwed,
    PaidForPast, PaidForPresent,
    CurrentPaid - PaidForPast - PaidForPresent AS PaidForFuture,
    [Description]
FROM Present_CTE s
LEFT JOIN tblProperty p ON p.ID = s.PropertyID
LEFT JOIN tblTenantTransCode c ON c.ID = s.TransactionCode

Vous pouvez éliminer les deux derniers CTE en écrivant l'ensemble de l'opération. PaidForXYZ dans leur intégralité plutôt que de les construire l'une sur l'autre, mais l'OMI estime que cela ne ferait qu'aggraver la situation. moins et je suis sûr que l'optimiseur le comprendra et transformera tout cela en expressions au lieu de sous-requêtes.

Aussi, je dois dire que toutes ces personnes ABS les blocs me rendent suspicieux. Je ne connais pas les détails de ce qui se passe ici, mais j'ai l'impression qu'ils sont utilisés à la place d'un opérateur de négation, peut-être un qui aurait dû être utilisé plus en amont (c'est-à-dire pour convertir les débits ou les crédits en montants négatifs) et cela pourrait conduire à des bogues subtils sur la route.

Answer 2

Tout d'abord, apprenez à formater le SQL pour que vous (et moi) puissions le lire :

SELECT OwnerName, 
       SUM(AmountPaid) AS Paid, 
       SUM(AmountOwedComplete) AS Owed, 
       SUM(AmountOwedThisMonth) AS OwedMonth,
       SUM(PaidForPast) AS PaidPast, 
       SUM(PaidForPresent) AS PaidPresent, 
       SUM((AmountPaid - PaidForPast - PaidForPresent)) AS PaidFuture, 
       [Description] 
  FROM (SELECT OwnerName, 
               AmountPaid, 
               AmountOwedComplete, 
               AmountOwedThisMonth, 
               PaidForPast, 
               [Description],
               (SELECT CASE WHEN (AmountPaid - PaidForPast) < ABS(AmountOwedThisMonth) 
                            THEN AmountPaid - PaidForPast
                       ELSE ABS(AmountOwedThisMonth) END) AS PaidForPresent
          FROM (SELECT OwnerName, 
                       AmountPaid, 
                       AmountOwedTotal - AmountPaid AS AmountOwedComplete,
                       AmountOwedThisMonth, 
                       (SELECT CASE WHEN (AmountPaid < ABS((AmountOwedTotal - AmountPaid)) + AmountOwedThisMonth)
                                    THEN AmountPaid 
                               ELSE ABS((AmountOwedTotal - AmountPaid)) + AmountOwedThisMonth END) AS PaidForPast,     
                               Description, 
                               TransactionDate
                          FROM (SELECT DISTINCT 
                                       t.TenantName, 
                                       p.PropertyName, 
                                       ISNULL(p.OwnerName, 'Uknown') AS OwnerName, 
                                       (SELECT SUM(Amount) 
                                          FROM tblTransaction 
                                         WHERE Amount > 0 
                                           AND TransactionDate >= @StartDate 
                                           AND TransactionDate <= @EndDate
                                           AND TenantID = t.ID 
                                           AND TransactionCode = trans.TransactionCode) AS AmountPaid, 
                                       (SELECT SUM(Amount) 
                                          FROM tblTransaction 
                                         WHERE tblTransaction.TransactionCode = trans.TransactionCode 
                                           AND tblTransaction.TenantID = t.ID)  AS AmountOwedTotal, 
                                       (SELECT SUM(Amount) 
                                          FROM tblTransaction 
                                         WHERE tblTransaction.TransactionCode = trans.TransactionCode 
                                           AND tblTransaction.TenantID = t.ID
                                           AND Amount < 0 
                                           AND TransactionDate >= @StartDate 
                                           AND TransactionDate <= @EndDate) AS AmountOwedThisMonth, 
                                       code.Description, 
                                       trans.TransactionDate 
                                  FROM tblTransaction trans 
                                  LEFT JOIN tblTenantTransCode code ON code.ID = trans.TransactionCode
                                  LEFT JOIN tblTenant t ON t.ID = trans.TenantID
                                  LEFT JOIN tblProperty p ON t.PropertyID  = p.ID
                                 WHERE trans.TransactionDate >= @StartDate 
                                   AND trans.TransactionDate <= @EndDate 
                                   AND trans.Amount > 0) q
               ) q2
       ) q3
 GROUP BY OwnerName, Description;

Deuxièmement, assurez-vous que vous disposez des bons index. Pour ce faire, vous devez être en mesure de lire le SQL.

Answer 3

Avez-vous votre mot à dire sur le schéma ? Dans l'état actuel des choses, il semble que vous essayez de générer un rapport à partir d'un magasin de données opérationnelles qui est de nature transactionnelle. Dans de nombreux scénarios à fort volume, il n'est pas rare de créer un schéma moins normalisé, appelé base de données d'aide à la décision, dans lequel vos données transactionnelles sont copiées/résumées à un intervalle particulier. Vous pouvez alors écrire des requêtes assez simples sur la base de données d'aide à la décision pendant que votre ODS hautement normalisé continue à fonctionner.