帮助用更好的代码替换这个 SQL 游标

Question

谁能帮助我提高 SQL 2000 中游标逻辑的性能。它在 SQl2005 和 SQL2008 中运行良好，但在 SQL 2000 中运行至少需要 20 分钟。顺便说一句，我永远不会选择使用游标，而且我没有编写此代码，只是想让它运行得更快。短期内无法将此客户端升级到 2005/2008。

-------------------------------------------------------------------------------
------- Rollup totals in the chart of accounts hierarchy
-------------------------------------------------------------------------------
DECLARE @B_SubTotalAccountID int, @B_Debits money, @B_Credits money, @B_YTDDebits money, @B_YTDCredits money

DECLARE Bal CURSOR FAST_FORWARD FOR 
 SELECT SubTotalAccountID, Debits, Credits, YTDDebits, YTDCredits FROM xxx 
  WHERE AccountType = 0 AND SubTotalAccountID Is Not Null and (abs(credits)+abs(debits)+abs(ytdcredits)+abs(ytddebits)<>0)
OPEN Bal

FETCH NEXT FROM Bal INTO @B_SubTotalAccountID, @B_Debits, @B_Credits, @B_YTDDebits, @B_YTDCredits

--For Each Active Account
WHILE @@FETCH_STATUS = 0
 BEGIN

 --Loop Until end of subtotal chain is reached
 WHILE @B_SubTotalAccountID Is Not Null
  BEGIN

  UPDATE xxx2
  SET Debits = Debits + @B_Debits,
   Credits = Credits + @B_Credits,
   YTDDebits = YTDDebits + @B_YTDDebits,
   YTDCredits = YTDCredits + @B_YTDCredits
  WHERE GLAccountID = @B_SubTotalAccountID

  SET @B_SubTotalAccountID = (SELECT SubTotalAccountID FROM xxx2 WHERE GLAccountID = @B_SubTotalAccountID)

  END

 FETCH NEXT FROM Bal INTO @B_SubTotalAccountID, @B_Debits, @B_Credits, @B_YTDDebits, @B_YTDCredits

 END

CLOSE Bal
DEALLOCATE Bal

Answer 1

一些建议：

1 - 使用探查器告诉您哪一部分运行缓慢 - 您可以获得每个语句的持续时间

2 - 在过程外部运行初始选择语句（游标声明）并检查查询计划。它跑得快吗？是否正确使用索引？

3 - 与更新语句相同 - 检查查询计划和索引使用情况

4 - 更新后的“set”语句看起来很奇怪 - 它似乎将一个值放入@B_SubTotalAccountID，然后立即由“fetch next”替换

Answer 2

Update xx2
Set Credits = Credits + X1.CreditTotal
    , Debits = Debits + X1.DebitTotal
    , YtdDebits = YtdDebits + X1.YtdDebitTotal
    , YtdCredits = YtdCredits + X1.YtdDebitTotal
From xx2 As X2
    Join    (
            Select SubTotalAccountID, Sum(Debits) As DebitTotal, Sum(Credits) As CreditTotal
                , Sum(YtdDebits) As YtdDebitTotal, Sum(YtdCredits) As YtdCreditTotal
            From xxx
            Where AccountType = 0 
                And SubTotalAccountID Is Not Null 
                And (
                    Credits <> 0
                    Or Debits <> 0
                    Or YtdCredits <> 0
                    Or YtdDebits <> 0
                    )
            Group By SubTotalAccountID
            ) As X1
        On X1.SubTotalAccountID = X2.GLAccountID

如果没有架构，我无法判断 xxx 表是否会为给定的 SubTotalAccountId 返回多行。我假设它可以按此列对值进行分组，以便每个 SubTotalAccountId 获得一行。

我还用简单的零检查替换了 WHERE 子句中对 ABS 的使用。这应该会快得多。

此 UPDATE 语句应该完全替代您的游标。