You read it right! Usually we speed queries up, this time you are asked to
slow it down!
For years the T-SQL forums/blogs were abuzz with the
fast multiple assignment operator single-update RUNNING TOTAL. While it always calculates correctly, there is
no guarantee that it follows the desired sort order since
the database engine works on
unordered sets. What is amazing about it, very hard to make it fail. Partitioned tables & parallel processing exluded.
The following
hybrid RUNNING TOTAL does:
1. Executes the fast single-update RUNNING TOTAL (0.7 sec execution time on average 500K rows)
2. It tests the results ordering
3. If not desired order, the control flow executes the
Paul Nielsen (www.sqlserverbible.com) cursor WHILE method (21 sec 500K records) which
guaranteed to work.
I put the single-update method through thousands of tests, it never flipped over to the Paul Nielsen method.
It would be nice to know when it fails, even if it is a statistical probability. Can you make it flip over to the Paul Nielsen method (fail the ordering test)?
Can you help?
Thanks.
-- T-SQL HYBRID RUNNING TOTAL calculation
-- Try Fast Method; if does not work, do slow method
-- SQL Server 2008 T-SQL code
USE tempdb;
SELECT SalesOrderID = ROW_NUMBER()
OVER (
ORDER BY (SELECT 1)),
OrderDate=CONVERT(date, OrderDate),
Sales=TotalDue,
RunningTotal=CONVERT(money, 0)
INTO Sales
FROM AdventureWorks2008.Sales.SalesOrderHeader
CROSS JOIN AdventureWorks2008.HumanResources.Department
ORDER BY SalesOrderID DESC
-- (503,440 row(s) affected)
-- Build clustered index to help the database engine
-- DESC sort is used for demo purposes only
CREATE CLUSTERED INDEX idxSOID
ON Sales (SalesOrderID DESC)
DECLARE @RunningTotal money = 0.0
BEGIN -- SINGLE-UPDATE FAST RUNNING-TOTAL
DBCC DROPCLEANBUFFERS
SET @RunningTotal = 0.0
UPDATE Sales
SET @RunningTotal = RunningTotal = @RunningTotal + Sales
OPTION (MAXDOP 1)
-- 1 sec
END
-- CHECK if RunningTotal is SalesOrderID order
-- IF not in desired order, perform the Paul Nielsen cursor WHILE method
IF 0 != (SELECT NoMatchInOrderCount=count(*)
FROM (SELECT ID1 = ROW_NUMBER()
OVER (
ORDER BY SalesOrderID DESC),
ID2 = ROW_NUMBER()
OVER (
ORDER BY RunningTotal ASC)
FROM Sales) x
WHERE ID1 != ID2)
BEGIN
-- PAUL NIELSEN method http://www.sqlusa.com/bestpractices2005/runningtotalusingcursor/
DBCC DROPCLEANBUFFERS
SET NOCOUNT ON
DECLARE @SalesOrderID INT,
@Sales MONEY
SET @RunningTotal = 0
DECLARE cRun CURSOR FAST_FORWARD FOR
SELECT SalesOrderID,
Sales
FROM Sales
ORDER BY SalesOrderID DESC
OPEN cRun
FETCH cRun INTO @SalesOrderID, @Sales -- prime the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RunningTotal = @RunningTotal + @Sales
UPDATE Sales
SET RunningTotal = @RunningTotal
WHERE SalesOrderID = @SalesOrderID
FETCH cRun INTO @SalesOrderID, @Sales -- fetch next
END
CLOSE cRun
DEALLOCATE cRun
-- 22 seconds
END
GO
SELECT TOP (10) *
FROM Sales
ORDER BY RunningTotal ASC
GO
/*
SalesOrderID OrderDate Sales RunningTotal
503440 2004-07-31 209.9169 209.9169
503439 2004-07-31 34.2219 244.1388
503438 2004-07-31 82.8529 326.9917
503437 2004-07-31 93.8808 420.8725
503436 2004-07-31 46.7194 467.5919
503435 2004-07-31 149.4292 617.0211
503434 2004-07-31 32.5754 649.5965
503433 2004-07-31 5.514 655.1105
503432 2004-07-31 88.9194 744.0299
503431 2004-07-31 23.7465 767.7764
*/
DROP TABLE tempdb.dbo.Sales
Kalman Toth
New Book:
Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
