Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

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

Print | posted on Monday, December 10, 2007 8:20 PM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

#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? ;)
12/16/2007 8:16 PM | Jesse

#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.
12/17/2007 9:46 PM |

#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
7/20/2009 8:21 AM | Jeff Moden
Comments have been closed on this topic.