Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Running Sum Query Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, September 22, 2007 1:04 PM
 SSC Eights! Group: General Forum Members Last Login: Tuesday, November 12, 2013 9:25 AM Points: 977, Visits: 250
 Comments posted to this topic are about the item Running Sum Query
Post #401446
 Posted Wednesday, April 11, 2012 8:47 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, September 25, 2013 4:16 AM Points: 6, Visits: 27
 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;
Post #1281605
 Posted Wednesday, April 11, 2012 8:50 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 3:52 PM Points: 22,082, Visits: 28,979
 nick.fairway (4/11/2012)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;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.
Post #1281611
 Posted Wednesday, April 11, 2012 9:08 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, September 25, 2013 4:16 AM Points: 6, Visits: 27
 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
Post #1281643
 Posted Wednesday, April 11, 2012 9:11 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 3:52 PM Points: 22,082, Visits: 28,979
 nick.fairway (4/11/2012)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.QtyOrderedReally want to see a high performance method? Read this and the discussion following it:http://www.sqlservercentral.com/articles/T-SQL/68467/
Post #1281648

 Permissions