Cumulative Totals Screencast

CURSOR must die  :)))

--00:02 Execution Time

SET NOCOUNT ON

GO

DECLARE @ComulativeTotal MONEY

SET @ComulativeTotal=0

UPDATE Sales.SalesOrderHeader

SET @ComulativeTotal=ComulativeTotal=@ComulativeTotal+ISNULL(TotalDue, 0)

Greate solution, Knyazev!

But how do you enforce any sort of ordering ?

A cumulative total normally needs to follow certain ordering rules to be useful.

OK!!!

--00:03 Execution Time

SET NOCOUNT ON

GO

CREATE TABLE #T (ID INT IDENTITY, SalesOrderID INT, TotalDue MONEY, ComulativeTotal MONEY)

INSERT INTO #T

SELECT SalesOrderID, TotalDue, ComulativeTotal

FROM Sales.SalesOrderHeader

ORDER BY SalesOrderID

DECLARE @ComulativeTotal MONEY

SET @ComulativeTotal=0;

UPDATE #T

SET @ComulativeTotal=ComulativeTotal=@ComulativeTotal+ISNULL(TotalDue, 0)

UPDATE t1

SET t1.ComulativeTotal=t2.ComulativeTotal

FROM Sales.SalesOrderHeader t1 INNER JOIN #T t2

ON t1.SalesOrderID=t2.SalesOrderID

DROP TABLE #T

SQLCLR, baby :-)

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/running-sums-yet-again-sqlclr-saves-the-day.aspx

Wow Knyazev!

Set-based programming at its greatest!

I love the Multiple Assignment set solution, Knyazev.

You actually don't need use #temp table to enforce the ordering.

Your original script has guranteed the ordering (by storage engine), because the query plan starts with an ordered clustered index scan.

UPDATE Sales.SalesOrderHeader

SET @ComulativeTotal=ComulativeTotal=@ComulativeTotal+ISNULL(TotalDue, 0)

The first operator shows:

                          |--Clustered Index Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]), ORDERED FORWARD)

David: Can you guarantee that the storage engine will always use an ordered scan?

Yes, because of the potential risk of page split issue on clustered index table , storage engine will always use ordered scan (even sometimes the paln does NOT show the ordered!). The only exception is you use Read UNCOMMITTED isolation.

Itzik presented this at devTeach Vancouver conference, I remember you were in that room too. :-)

SQL412 What's Between Index Internals, Isolation Levels and Data Consistency

Hi David Wei, I disagree that "original script has guranteed the ordering (by storage engine)". Instead I'd say that the current behavior of the engine uses a clustered index and produces the correct order, but the T-SQL command does not declare the order so there is no guarantee of the same behavior in the future. It’s a question of whether you want to trust in the continued behavior of the engine to produce the desired result, or specify in code the desired result. Because the row order is paramount to a cumulative total, you can’t risk missing the correct order sometime in the future.

I love, too, Multiple Assignment Variables, and I’ve written about them before. But in this case, until Microsoft allows a syntax that can specify the order by for Multiple Assignment Variables, the safest T-SQL solution is the cursor, or Adam’s CLR cursor. It makes a great demo, but it’s not production safe.

David,

I think you might be confusing this with something else Itzik said.  I didn't remember hearing that, so I checked in with Itzik this afternoon and asked.  He confirmed that there is definitely no guarantee of ordering without on ORDER BY clause.

Ok, I agree that "if you don't use order by, it up to storage engine to decide how to access the data. So ordered scan on clustered index table is not guaranteed by any supported document."

Based on above discussions, Knyazev’s 2nd script still not guarantees the ordering when update #t (because no order by)

To force an ordered scan, we need join the same table again, the MERGE join hint requires the ordered scan on both input.

(Without the hint, query optimizer also choose merge join, but we explicit use the hint to GUARANTEE the ordering)

DECLARE @CumulativeTotal MONEY

SET @CumulativeTotal=0

UPDATE s1

SET @CumulativeTotal=s1.CumulativeTotal=@CumulativeTotal+ISNULL(s1.TotalDue, 0)

from Sales.SalesOrderHeader s1

inner merge join Sales.SalesOrderHeader s2 on s1.SalesOrderID = s2.SalesOrderID

So what happens to this query when it gets parallelized?  Now you really don't have any control over the splits of the load, right?  And if a run is spread across more than one thread then you are hosed it would seem.  I would think that the MERGE join trick is also succeptable to this issue.  

Bottom line is to remember (as I understand it):  in set-based processing there is NO guaranteed ordering of sets except on output after an order by clause.

I redid Paul Nielsen's original WHILE loop.

It runs in 2 sec.  Much faster than the 80 sec correlated subquery!

However, it is not a clean query anymore since update is used.

======================================

DBCC dropcleanbuffers

SET NoCount ON

DECLARE

 @SalesOrderID INT,

 @TotalDue MONEY,

 @CumuTot MONEY

SELECT SalesOrderID, OrderDate, TotalDue, CumuTot = convert(money,0.0)

into #Accum

FROM Sales.SalesOrderHeader

ORDER BY OrderDate, SalesOrderID

create index #idxAccum on #Accum( SalesOrderID)

SET @CumuTot = 0

 DECLARE curRunTot CURSOR FAST_FORWARD

   FOR

     SELECT SalesOrderID, TotalDue

       FROM #Accum

       ORDER BY OrderDate, SalesOrderID

 OPEN curRunTot

 FETCH curRunTot INTO @SalesOrderID, @TotalDue  

 WHILE @@Fetch_Status = 0

   BEGIN

     SET @CumuTot = @CumuTot + @TotalDue

     UPDATE #Accum

       SET CumuTot = @CumuTot

       WHERE SalesOrderID = @SalesOrderID    

     FETCH curRunTot INTO @SalesOrderID, @TotalDue  

   END

 CLOSE curRunTot

  DEALLOCATE curRunTot

SELECT SalesOrderID, TotalDue, CumuTot

 FROM #Accum

 ORDER BY OrderDate, SalesOrderID

Kevin: (1st) update is never executed using parallelism, but if you want to GUARANTEE the "Guarantee" , add OPTION (MAXDOP 1) as you wish.

(2nd) don't confued by the name "SET", set-based is still processed internally ROW by ROW. each iterator calls it's child getRow method repeatedly until it returned all rows. The Merge join read sorted 2 inputs, compare row by row. If two rows are matched, it output it and move to next; if not match, it discards the lower one, and move to next. The output ordering of the merge join is reserved. (for ex: when your query uses order by on the merge key, you will never see sql server use a sort iterator after the merge, that's the reason). The next iterator, comput scalar also processes ROW by ROW for each row output from the MERGE. This is perfect safe for the ordering.

Kalman: your script is an improvement by moving only few columns into temp table, thus, reduced the read, but this still can not compete with the beautiful SET solution. (BTW, you still need update back the original table by join the #accum).

Also, we have been talking a lot about the dfault behaior of order scan on clustered index, I think it worth to past a link to Paul Randal's blog : When can allocation order scans be used? (Though it is not officially documented :)

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/11/09/when-can-allocation-order-scans-be-used.aspx

We are working on SQL server database, not just T-SQL language. There are situations we need the advanced hint to guide the query engine to boost performance. Personally, I don't agree that "CURSOR must die", but in this particular case, I vote the set solution,especially when you are dealing with large tables :-)

Cheers.

I am aware that set-based processing is actually row-by-row when it gets down to the nitty gritty inside the engine.  :-)  My comment about parallelization was directed at the numerous examples of this technique I have seen on the web that are SELECTs vice UPDATEs.  Report queries often have a need for this.  Does anyone know definitively if my supposition is correct - that parallelization of a query can also lead to "out-of-order" processing and thus incorrect results in a query such as the one in this thread?