Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Running Sum Query Expand / Collapse
Author
Message
Posted Saturday, September 22, 2007 1:04 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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.MTD
FROM
(
SELECT
ProductKey
, OrderDate
, MTD= (SELECT sum(QtyOrdered) FROM #t WHERE OrderDate <= a.OrderDate AND ProductKey = a.ProductKey)
FROM #t A
GROUP BY ProductKey, OrderDate
) G
JOIN #t F
ON G.ProductKey = F.ProductKey AND G.OrderDate = F.OrderDate
GROUP BY G.ProductKey,G.OrderDate, G.MTD
ORDER BY G.ProductKey,G.OrderDate;
Post #1281605
Posted Wednesday, April 11, 2012 8:50 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 20,685, Visits: 32,290
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.MTD
FROM
(
SELECT
ProductKey
, OrderDate
, MTD= (SELECT sum(QtyOrdered) FROM #t WHERE OrderDate <= a.OrderDate AND ProductKey = a.ProductKey)
FROM #t A
GROUP BY ProductKey, OrderDate
) G
JOIN #t F
ON G.ProductKey = F.ProductKey AND G.OrderDate = F.OrderDate
GROUP BY G.ProductKey,G.OrderDate, G.MTD
ORDER 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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1281611
Posted Wednesday, April 11, 2012 9:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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) a
CROSS JOIN #t b
WHERE (b.OrderDate <= a.OrderDate AND a.ProductKey = b.ProductKey)
GROUP BY a.OrderDate, a.QtyOrdered, a.ProductKey
ORDER BY a.ProductKey, a.OrderDate, a.QtyOrdered
Post #1281643
Posted Wednesday, April 11, 2012 9:11 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 20,685, Visits: 32,290
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) a
CROSS JOIN #t b
WHERE (b.OrderDate <= a.OrderDate AND a.ProductKey = b.ProductKey)
GROUP BY a.OrderDate, a.QtyOrdered, a.ProductKey
ORDER BY a.ProductKey, a.OrderDate, a.QtyOrdered


Really want to see a high performance method? Read this and the discussion following it:
http://www.sqlservercentral.com/articles/T-SQL/68467/



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1281648
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse