Cursor is really faster than set-based solution for weighted moving average?
Today, I was involved in an interesting discussion.
Someone asked for a moving average solution. I joined the discussion late.
The previous solutions and mine were all set-based and very slow. Then Jezemine come up with a CURSOR solution that looked fine and fast.
After some trial-and-errors I finally posted a set-based solution that seems to be the fastest solution yet.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911
So it still seems that [proper implemented] set-based solutions are the fastest, compared to CURSORs.
Below is also an implementation of a SQL Server 2005 approach.
DECLARE @Sample TABLE (dt DATETIME, Rate FLOAT)
INSERT @Sample
SELECT CURRENT_TIMESTAMP - 10, 1 UNION ALL
SELECT CURRENT_TIMESTAMP - 9, 2 UNION ALL
SELECT CURRENT_TIMESTAMP - 8, 4 UNION ALL
SELECT CURRENT_TIMESTAMP - 7, 4 UNION ALL
SELECT CURRENT_TIMESTAMP - 6, 5 UNION ALL
SELECT CURRENT_TIMESTAMP - 5, 6 UNION ALL
SELECT CURRENT_TIMESTAMP - 4, 6 UNION ALL
SELECT CURRENT_TIMESTAMP - 3, 8 UNION ALL
SELECT CURRENT_TIMESTAMP - 2, 9 UNION ALL
SELECT CURRENT_TIMESTAMP - 1, 10 UNION ALL
SELECT CURRENT_TIMESTAMP - 0, 11 UNION ALL
SELECT CURRENT_TIMESTAMP + 1, 9
-- SQL Server 2005
SELECT DATEADD(DAY, p.ActualDate, '19000101') AS dt,
AVG(p.Rate) AS SimpleMovingAvg,
SUM(
CASE p.dt
WHEN 'dt0' THEN p.r0
WHEN 'dt1' THEN p.r1
WHEN 'dt2' THEN p.r2
ELSE 0
END
) AS WeightedMovingAvg
FROM (
SELECT Rate,
DATEDIFF(DAY, '19000101', dt) AS dt0,
0.7 * Rate AS r0,
DATEDIFF(DAY, '18991231', dt) AS dt1,
0.2 * Rate AS r1,
DATEDIFF(DAY, '18991230', dt) AS dt2,
0.1 * Rate AS r2
FROM @Sample
) AS y
UNPIVOT (
ActualDate
FOR dt IN (y.dt0, y.dt1, y.dt2)
) AS p
GROUP BY p.ActualDate
HAVING MIN(p.dt) = 'dt0'
ORDER BY p.ActualDate
-- SQL Server 2000
SELECT DATEADD(DAY, k.dt, '19000101') AS dt,
AVG(k.Rate) AS SimpleMovingAvg,
SUM(k.wr) AS WeightedMovingAvg
FROM (
SELECT DATEDIFF(DAY, '18991230', dt) AS dt,
0.1 * Rate AS wr,
Rate,
0 AS Actualdate
FROM @Sample
UNION ALL
SELECT DATEDIFF(DAY, '18991231', dt),
0.2 * Rate,
Rate,
0
FROM @Sample
UNION ALL
SELECT DATEDIFF(DAY, '19000101', dt),
0.7 * Rate,
Rate,
1
FROM @Sample
) AS k
GROUP BY k.dt
HAVING MAX(k.Actualdate) = 1
ORDER BY k.dt
Or simply use this rewrite...
SELECT DATEADD(DAY, v.Number, s.dt),
AVG(1.0E0 * s.Rate)
FROM @Sample AS s
INNER JOIN master..spt_values AS v ON v.Type = 'P'
WHERE v.Number < 3
GROUP BY DATEADD(DAY, v.Number, t.dt)
HAVING COUNT(*) = 3
ORDER BY DATEADD(DAY, v.Number, t.dt) DESC
Or simply use this rewrite...
SELECT DATEADD(DAY, v.Number, s.dt),
AVG(1.0E0 * s.Rate)
FROM @Sample AS s
INNER JOIN master..spt_values AS v ON v.Type = 'P'
WHERE v.Number < 3
GROUP BY DATEADD(DAY, v.Number, t.dt)
HAVING COUNT(*) = 3
ORDER BY DATEADD(DAY, v.Number, t.dt) DESC
Legacy Comments
|
Jesse
2007-12-16 |
re: Cursor is really faster than set-based solution? I think a loop will still be faster for running totals though, since for a running total you have to look at all the rows preceding the current row, rather than just the preceding few. want to prove me wrong? ;) |
|
Peso
2007-12-17 |
re: Cursor is really faster than set-based solution for weighted moving average? No... I can't. With a running total there can be any number of records. With moving average there are a finite number of revords. |
|
Jeff Moden
2009-07-20 |
re: Cursor is really faster than set-based solution for weighted moving average? Jesse wrote: I think a loop will still be faster for running totals though, since for a running total you have to look at all the rows preceding the current row, rather than just the preceding few. want to prove me wrong? ;) ------------------------------------------- The only proper way to drop such a gauntlet is with code Jesse. The onus is on the challenger. ;-) --Jeff Moden |