Help replace this SQL cursor with better code
- by user318573
Can anyone give me a hand improving the performance of this cursor logic from SQL 2000. It runs great in SQl2005 and SQL2008, but takes at least 20 minutes to run in SQL 2000. BTW, I would never choose to use a cursor, and I didn't write this code, just trying to get it to run faster. Upgrading this client to 2005/2008 is not an option in the immediate future.
-------------------------------------------------------------------------------
------- 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
