September 22, 2007 at 1:04 pm
Comments posted to this topic are about the item Running Sum Query
April 11, 2012 at 8:47 am
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;
April 11, 2012 at 8:50 am
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.
April 11, 2012 at 9:08 am
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
April 11, 2012 at 9:11 am
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:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy