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