SQL Server guru Adam Machanic posted a really interesting post on his blog yesterday in regard to calculating running totals in SQL Server. I'm sure Adam won't mind me saying he seemed a little embarrassed that his own logic had been outperformed by that most resource voracious of SQL Server constructs - a cursor.
Adam's own set-based solution had to be cancelled at 5 minutes whilst the cursor based solution executed in 14 seconds. I actually ran the same on my machine and it took 8.5 seconds (yes, I cleared the caches first). Not bad at all for a cursor over 100000+ rows.
I reckon I've come up with an even quicker solution though and Adam will be delighted to know that there isn't a cursor in sight. Here's my code:
DBCC
DROPCLEANBUFFERS -- Clears the data cache
DBCC FREEPROCCACHE -- Clears the procedure cache
GO
declare @start datetime
set @start = getdate()
declare @runningtotal money
set @runningtotal=0.0
DECLARE @Results TABLE
(
TransactionID INT NOT NULL PRIMARY KEY,
ActualCost MONEY,
RunningTotal MONEY
)
insert into @Results (TransactionID, ActualCost)
select TransactionID, ActualCost
from Production.TransactionHistory
order by TransactionID
update @Results
set @runningtotal = RunningTotal = @runningtotal + ActualCost
select *
from @Results
ORDER BY TransactionID
print 'time taken = ' + cast(datediff(ms, @start, getdate()) as varchar(20))
As you can probably see, the real power here is in the UPDATE statement which updates @runningtotal at the same time as updating the table. Its a little-known feature of SQL Server but one that can be very powerful. In my tests this executed in 4 seconds, less than half the time of cursor solution.
5 minutes and counting down to 4seconds. Very handy indeed!
-Jamie
P.S. You'll need the AdventureWorks sample DB installed somewhere in order to make it work!