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
declare @start datetime
set @start = getdate()
declare @runningtotal money
DECLARE @Results TABLE
TransactionID INT NOT NULL PRIMARY KEY,
insert into @Results (TransactionID, ActualCost)
select TransactionID, ActualCost
order by TransactionID
set @runningtotal = RunningTotal = @runningtotal + ActualCost
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!
P.S. You'll need the AdventureWorks sample DB installed somewhere in order to make it work!