SQLServerCentral / Discuss content posted by Elmer Miller / Article Discussions / Article Discussions by Author / Running Sum Query / Latest Posts

RE: Running Sum Query

Lynn Pettis — Wed, 11 Apr 2012 09:11:27 GMT

[quote][b]nick.fairway (4/11/2012)[/b][hr]Even more efficient for the test data supplied is this one - almost 9 times faster than the original -- look at the execution plan of the batch when you run them side by side:SELECT a.ProductKey, a.OrderDate, a.QtyOrdered, SUM(b.QtyOrdered)FROM ( SELECT t.OrderDate, t.ProductKey, QtyOrdered = sum(t.QtyOrdered) FROM #t t GROUP BY t.OrderDate,t.ProductKey) aCROSS JOIN #t bWHERE (b.OrderDate <= a.OrderDate AND a.ProductKey = b.ProductKey)GROUP BY a.OrderDate, a.QtyOrdered, a.ProductKeyORDER BY a.ProductKey, a.OrderDate, a.QtyOrdered[/quote]Really want to see a high performance method? Read this and the discussion following it:[b][url]http://www.sqlservercentral.com/articles/T-SQL/68467/[/url][/b]

RE: Running Sum Query

nick.fairway — Wed, 11 Apr 2012 09:08:15 GMT

Even more efficient for the test data supplied is this one - almost 9 times faster than the original -- look at the execution plan of the batch when you run them side by side:SELECT a.ProductKey, a.OrderDate, a.QtyOrdered, SUM(b.QtyOrdered)FROM ( SELECT t.OrderDate, t.ProductKey, QtyOrdered = sum(t.QtyOrdered) FROM #t t GROUP BY t.OrderDate,t.ProductKey) aCROSS JOIN #t bWHERE (b.OrderDate <= a.OrderDate AND a.ProductKey = b.ProductKey)GROUP BY a.OrderDate, a.QtyOrdered, a.ProductKeyORDER BY a.ProductKey, a.OrderDate, a.QtyOrdered

RE: Running Sum Query

Lynn Pettis — Wed, 11 Apr 2012 08:50:47 GMT

[quote][b]nick.fairway (4/11/2012)[/b][hr]A much more efficient version of this takes about 1/6 of the processing:SELECT G.OrderDate, G.ProductKey, QtyOrdered = SUM(F.QtyOrdered), G.MTDFROM( SELECT ProductKey , OrderDate , MTD= (SELECT sum(QtyOrdered) FROM #t WHERE OrderDate <= a.OrderDate AND ProductKey = a.ProductKey) FROM #t A GROUP BY ProductKey, OrderDate) GJOIN #t FON G.ProductKey = F.ProductKey AND G.OrderDate = F.OrderDateGROUP BY G.ProductKey,G.OrderDate, G.MTDORDER BY G.ProductKey,G.OrderDate;[/quote]Nice triangular join. As the number of records increase, this will bring your server to its knees. In fact, at that point, even a cursor-based solution will run faster.

RE: Running Sum Query

nick.fairway — Wed, 11 Apr 2012 08:47:08 GMT

A much more efficient version of this takes about 1/6 of the processing:SELECT G.OrderDate, G.ProductKey, QtyOrdered = SUM(F.QtyOrdered), G.MTDFROM( SELECT ProductKey , OrderDate , MTD= (SELECT sum(QtyOrdered) FROM #t WHERE OrderDate <= a.OrderDate AND ProductKey = a.ProductKey) FROM #t A GROUP BY ProductKey, OrderDate) GJOIN #t FON G.ProductKey = F.ProductKey AND G.OrderDate = F.OrderDateGROUP BY G.ProductKey,G.OrderDate, G.MTDORDER BY G.ProductKey,G.OrderDate;

Running Sum Query

Elmer Miller — Sat, 22 Sep 2007 13:04:20 GMT

Comments posted to this topic are about the item [B]Running Sum Query[/B]