Most Efficient Way to Compute Running Value in SQL

Question

Possible Duplicate:
Calculate a Running Total in SqlServer

Consider this data

     Day | OrderCount
       1       3
       2       2 
       3       11
       4       3
       5       6

How can i get this accumulation of OrderCount(running value) resultset using T-SQL query

      Day | OrderCount | OrderCountRunningValue
       1        3            3
       2        2            5
       3        11           16
       4        3            19
       5        6            25

I Can easily do this with looping in the actual query (using #table) or in my C# codebehind but its so slow (Considering that i also get the orders per day) when im processing thousand of records so i'm looking for better / more efficient approach hopefully without loops something like recursing CTE or something else.

Any idea would be greatly appreciated. TIA

There are many answers to running total problems for SQL on SO. Look at http://stackoverflow.com/questions/7357516/subquery-or-leftjoin-with-group-by-which-one-is-faster for more details. Or this document http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc — MatBailie, Oct 26 '11 at 14:57
Note: The questions and docs referenced above show that cursors are faster than half-Cartesian-products for almost any data size. I strongly suggest avoiding such solutions. — MatBailie, Oct 26 '11 at 15:06

score 2 · Answer 1 · answered Oct 26 '11 at 14:53

2

SELECT t.Day, 
       t.OrderCount, 
       (SELECT SUM(t1.OrderCount) FROM table t1 WHERE t1.Day <= t.Day) 
         AS OrderCountRunningValue
FROM table t

answered Oct 26 '11 at 14:53

kgautron

7,915
9
39
60

1

Note that this scales poorly over larger data sets. The 100th record will compute 100 rows. The 101st will recompute all of those rows, and one more. etc, etc. It's the equivalent of a half-Cartesian-product. – MatBailie Oct 26 '11 at 14:56

score 2 · Accepted Answer · answered Oct 26 '11 at 15:19

As you seem to need these results in the client rather than for use within another SQL query, you are probably better off Not doing this in SQL.

(The linked question in my comment shows 'the best' option within SQL, if that is infact necessary.)

What may be recommended is to pull the Day and OrderCount values as one result set (SELECT day, orderCount FROM yourTable ORDER BY day) and then calculate the running total in your C#.

Your C# code will be able to iterate through the dataset efficiently, and will almost certainly outperform the SQL approaches. What this does do, is to transfer some load from the SQL Server to the web-server, but at an overall (and significant) resource saving.

score 1 · Answer 3 · edited Oct 26 '11 at 15:02

1

SELECT 
  t.day, 
  t.orderCount, 
  SUM(t1.orderCount) orderCountRunningValue
FROM 
  table t INNER JOIN table t1 ON t1.day <= t.day
group by t.day,t.orderCount

edited Oct 26 '11 at 15:02

MatBailie

83,401
18
103
137

answered Oct 26 '11 at 15:00

StevieG

8,639
23
31

1

Same consideration as KayKay's answer as (although it uses a different expression) it follows the same logic of a half-Cartesian-product. – MatBailie Oct 26 '11 at 15:01
ah i get it, same table but differnt alias – dotnetlinc Oct 26 '11 at 15:19

score 0 · Answer 4 · answered Oct 26 '11 at 16:10

CTE's to the rescue (again):

DROP TABLE tmp.sums;
CREATE TABLE tmp.sums
        ( id INTEGER NOT NULL
        , zdate timestamp not null
        , amount integer NOT NULL
        );

INSERT INTO tmp.sums (id,zdate,amount) VALUES
 (1, '2011-10-24', 1 ),(1, '2011-10-25', 2 ),(1, '2011-10-26', 3 )
,(2, '2011-10-24', 11 ),(2, '2011-10-25', 12 ),(2, '2011-10-26', 13 )
        ;

WITH RECURSIVE list AS (
-- Terminal part
    SELECT  t0.id, t0.zdate
    , t0.amount AS amount
    , t0.amount AS runsum
    FROM tmp.sums t0
    WHERE NOT EXISTS (
        SELECT * FROM tmp.sums px
        WHERE px.id = t0.id
        AND px.zdate < t0.zdate
        )
    UNION
    -- Recursive part
    SELECT  p1.id AS id
    , p1.zdate AS zdate
    , p1.amount AS amount
    , p0.runsum + p1.amount AS runsum
    FROM tmp.sums AS p1
    , list AS p0
    WHERE p1.id = p0.id
    AND p0.zdate < p1.zdate
    AND NOT EXISTS (
        SELECT * FROM tmp.sums px
        WHERE px.id = p1.id
        AND px.zdate < p1.zdate
        AND px.zdate > p0.zdate
        )
    )
SELECT * FROM list
ORDER BY id, zdate;

The output:

DROP TABLE
CREATE TABLE
INSERT 0 6
 id |        zdate        | amount | runsum 
----+---------------------+--------+--------
  1 | 2011-10-24 00:00:00 |      1 |      1
  1 | 2011-10-25 00:00:00 |      2 |      3
  1 | 2011-10-26 00:00:00 |      3 |      6
  2 | 2011-10-24 00:00:00 |     11 |     11
  2 | 2011-10-25 00:00:00 |     12 |     23
  2 | 2011-10-26 00:00:00 |     13 |     36
(6 rows)

You mean like in the questions mentioned in the comments of the question? Where it has been suggested this is a duplicate question, and looking at those questions will provide answers like this? — MatBailie, Oct 26 '11 at 16:52
No, I don't look into duplicates. This is a 100 % original answer. I doubt if anybody ever came up with a recursive solution. — wildplasser, Oct 26 '11 at 17:02
Now I see there is one. Martin Smith. No bad company for me. — wildplasser, Oct 26 '11 at 17:05

Most Efficient Way to Compute Running Value in SQL

4 Answers4