代码之家 › 专栏 › 技术社区 › Malfist

帮助我重构这个查询怪兽

tsql sql-server

Malfist · 技术社区 · 15 年前

这是一个巨大的怪物,它将进入一个SP,所以变量是可用的:

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

这就是它的作用。它拜访了所有的房客,得到了他们这个月所付的以及他们所欠的一切。然后它计算以前的费用、本月的费用和将来的费用。然后,根据对押记的描述和财产所有人的姓名对其进行合计。

它看起来很可怕,我想知道是否有一些捷径我错过了,可以使用。

3 回复 | 直到 15 年前

Aaronaught 15 年前

这里的凶手是 PaidForPast (派生) PaidForPresent )计算,这似乎是试图表明一个租户是否支付他的余额是完整的(如果我理解正确-这是不容易的)。这一计算实际上必须针对每一个支付交易执行,并且取决于从整个租户历史记录中派生的另一个合计,这保证了O(n^2)操作。

这对您的数据库来说是一件残忍、残酷的事情,虽然我可能会使您的查询看起来更漂亮,但是如果您被迫根据此查询所暗示的特定可用数据集生成信息,那么您就无法从性能问题中解脱出来。

这里的内容不是重构/优化问题,而是一个严重的设计问题。实际上,你有两个问题。较小的问题是,您正在将业务逻辑编码到数据层中;较大的问题是,系统的设计并不是为了跟踪它实际需要的所有信息。

基本上 全部的 您在此查询中生成的信息应保存在某种类型的应收历史记录表中,该表记录每个交易的这些汇总/统计信息。该表可以由应用程序本身或您的 tblTransaction 表。

可能不是您要寻找的答案,但实际上我已经完成了重构的一半,这时我意识到不可能删除 PaidForXYZ 嵌套。

事实上,产生这些结果的最快方法可能是使用光标,因为这样您就可以使用部分聚合并将其转换为O(N)操作。但是您真的想要一个光标在整个事务表上运行而不使用过滤器吗?

更新:

如果您真的不关心性能,只想清理它,这样更容易阅读/维护,下面是我能想到的使用一些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

你可以通过写出整个 帕德福西兹 完全的表达,而不是一个一个地建立在另一个之上,但在我看来,这最终会使它 较少的 可读性强,我确信优化器会找到它,并将其全部映射到表达式中,而不是子查询中。

另外,我不得不说所有这些 ABS 积木让我怀疑。我不知道这里发生了什么,但感觉它们被用来代替否定运算符,可能是应该在更上游使用的运算符(即,将借项或贷项转换为负数),这可能会导致一些细微的错误。

Dinah SLaks 15 年前

首先,学习格式化SQL,以便您(和我)可以阅读它:

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;

第二,确保您有正确的索引——您需要能够读取SQL才能做到这一点。

Jesse C. Slicer 15 年前

你对这个模式有什么看法吗?现在看来,您似乎正在尝试生成一个操作数据存储的报告,该操作数据存储本质上是事务性的。在许多大容量的场景中,创建一个不太规范的模式(称为决策支持数据库),并在特定的时间间隔内将事务数据复制/汇总到该数据库,这并不少见。然后,您可以针对DSS编写非常简单的查询,而您的高度规范化的ODS则继续运行。