Thinking outside the box

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

My Links

Advertisement

News

Archives

Post Categories

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 ]

Feedback

Gravatar

# 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
Gravatar

# 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 | Peso
Gravatar

# 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.

Powered by:
Powered By Subtext Powered By ASP.NET