How to get a running total with MSSQL

There are basically 4 ways of producing running totals in Microsoft SQL Server:

  1. The front end, the report writer
  2. Cross joins
  3. Sub-query
  4. Cursors
  • I will NOT provide a cursor example, based on religious grounds.
  • I do not believe in cursors. SQL is a set language, not a record based language.
  • In this case cursors are lot more efficient on small data sets, but grind to a halt on large data sets.
  • Microsoft SQL Server does NOT support the correct way of producing running totals.
  • The proper way of producing running totals is to use the windowing of the sum.
  • Oracle and DB2 do support the windowing on the sum. 
select abc, amount, sum(amount) over (order by amount, abc) as 'Running total'
from xyz
order by 2;

Data used

USE adventureworks;
go

SET STATISTICS TIME ON
SET STATISTICS IO ON
SELECT customerid,
       salesorderid,
       subtotal AS 'Order amount'
FROM sales.salesorderheader
WHERE customerid <= 3
ORDER BY customerid, salesorderid;
go
customerid  salesorderid Order amount
----------- ------------ ---------------------
1           43860        13216.0537
1           44501        23646.0339
1           45283        34066.1881
1           46042        31423.5209
2           46976        9216.3596
2           47997        4949.8589
2           49054        1574.1247
2           50216        1751.5987
2           51728        3534.1671
2           57044        4106.6501
2           63198        3668.7336
2           69488        822.009
3           44124        18555.8149
3           44791        17419.6078
3           45568        18285.6382
3           46377        11796.1804
3           47439        71112.1316
3           48378        51198.5404
3           49538        27899.9579
3           50748        47138.0027
3           53616        83436.1754
3           59011        40109.0555
3           65310        15431.5196
3           71889        31559.7518

(24 row(s) affected)
Table 'SalesOrderHeader'. Scan count 1, logical reads 74, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 1 ms.
SQL Server parse and compile time: 
   CPU time = 10 ms, elapsed time = 10 ms.

Cross joins

  1. A cross join returns the cartesian product of two joined tables.
  2. A cartesian product is a set of all the possible combinations.
  3. There are 2 ways of doing a cross join, and both produce the same result:
    1. Implicit: select ... from abc, xyz;
    2. Explicit: select ... from abc cross join xyz

Implicit cross join

SELECT a.customerid,
       a.salesorderid,
       a.subtotal AS 'Order amount',
       SUM(b.subtotal) AS 'Running total'
FROM sales.salesorderheader a,
     sales.salesorderheader b
WHERE (a.customerid >= b.customerid) and
      (a.salesorderid >= b.salesorderid) and
      (a.customerid <= 3)
GROUP BY a.customerid, a.salesorderid, a.subtotal
ORDER BY a.customerid
go
customerid  salesorderid Order amount          Running total
----------- ------------ --------------------- ---------------------
1           43860        13216.0537            13216.0537
1           44501        23646.0339            36862.0876
1           45283        34066.1881            70928.2757
1           46042        31423.5209            102351.7966
2           46976        9216.3596             111568.1562
2           47997        4949.8589             116518.0151
2           49054        1574.1247             118092.1398
2           50216        1751.5987             119843.7385
2           51728        3534.1671             123377.9056
2           57044        4106.6501             127484.5557
2           63198        3668.7336             131153.2893
2           69488        822.009               131975.2983
3           71889        31559.7518            565917.6745
3           65310        15431.5196            533535.9137
3           59011        40109.0555            514435.6605
3           53616        83436.1754            470219.9549
3           50748        47138.0027            383249.6124
3           49538        27899.9579            334360.011
3           48378        51198.5404            304885.9284
3           47439        71112.1316            248737.5291
3           46377        11796.1804            168409.0379
3           45568        18285.6382            125189.3366
3           44791        17419.6078            72837.5103
3           44124        18555.8149            31771.8686

(24 row(s) affected)

Table 'SalesOrderHeader'. Scan count 25, logical reads 847, physical reads 29, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 8 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 10 ms.

Explicit cross join

SELECT a.customerid,
       a.salesorderid,
       a.subtotal AS 'Order amount',
       SUM(b.subtotal) AS RunningTotal
FROM sales.salesorderheader a CROSS JOIN sales.salesorderheader b
WHERE (b.customerid <= a.customerid) and
      (b.salesorderid <= a.salesorderid) and
      (a.customerid <= 3)
GROUP BY a.customerid,a.salesorderid,a.subtotal
ORDER BY a.customerid,a.salesorderid;
go
customerid  salesorderid Order amount          RunningTotal
----------- ------------ --------------------- ---------------------
1           43860        13216.0537            13216.0537
1           44501        23646.0339            36862.0876
1           45283        34066.1881            70928.2757
1           46042        31423.5209            102351.7966
2           46976        9216.3596             111568.1562
2           47997        4949.8589             116518.0151
2           49054        1574.1247             118092.1398
2           50216        1751.5987             119843.7385
2           51728        3534.1671             123377.9056
2           57044        4106.6501             127484.5557
2           63198        3668.7336             131153.2893
2           69488        822.009               131975.2983
3           44124        18555.8149            31771.8686
3           44791        17419.6078            72837.5103
3           45568        18285.6382            125189.3366
3           46377        11796.1804            168409.0379
3           47439        71112.1316            248737.5291
3           48378        51198.5404            304885.9284
3           49538        27899.9579            334360.011
3           50748        47138.0027            383249.6124
3           53616        83436.1754            470219.9549
3           59011        40109.0555            514435.6605
3           65310        15431.5196            533535.9137
3           71889        31559.7518            565917.6745

(24 row(s) affected)

Table 'SalesOrderHeader'. Scan count 25, logical reads 847, physical reads 29, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 3 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 7 ms.

Look at the execution times:

  1. The implicit cross join: CPU time = 0 ms, elapsed time = 8 ms.
  2. The explicit cross join: CPU time = 0 ms, elapsed time = 3 ms.

What a difference! Even the compile time for the explicit cross join is faster.

Subtotal subquery

SELECT a.customerid,
       a.salesorderid,
       a.subtotal,
      (SELECT SUM(b.subtotal)
       FROM sales.salesorderheader b
       WHERE b.customerid <= a.customerid and
             b.salesorderid <= a.salesorderid) AS 'Running total'
FROM sales.salesorderheader a
WHERE a.customerid <= 3
ORDER BY a.customerid, a.salesorderid
customerid  salesorderid subtotal              Running total
----------- ------------ --------------------- ---------------------
1           43860        13216.0537            13216.0537
1           44501        23646.0339            36862.0876
1           45283        34066.1881            70928.2757
1           46042        31423.5209            102351.7966
2           46976        9216.3596             111568.1562
2           47997        4949.8589             116518.0151
2           49054        1574.1247             118092.1398
2           50216        1751.5987             119843.7385
2           51728        3534.1671             123377.9056
2           57044        4106.6501             127484.5557
2           63198        3668.7336             131153.2893
2           69488        822.009               131975.2983
3           44124        18555.8149            31771.8686
3           44791        17419.6078            72837.5103
3           45568        18285.6382            125189.3366
3           46377        11796.1804            168409.0379
3           47439        71112.1316            248737.5291
3           48378        51198.5404            304885.9284
3           49538        27899.9579            334360.011
3           50748        47138.0027            383249.6124
3           53616        83436.1754            470219.9549
3           59011        40109.0555            514435.6605
3           65310        15431.5196            533535.9137
3           71889        31559.7518            565917.6745

(24 row(s) affected)

Table 'SalesOrderHeader'. Scan count 25, logical reads 16946, physical reads 6, read-ahead reads 699, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 94 ms,  elapsed time = 113 ms.

Conclusion

The fastest is:

  1. The front end, the report writer. It does NOT any CPU cycle of the SQL server.
  2. Cross-joins
  3. Sub-query

I expected the sub-query to be slower, but not that slow!

  1. Cursors are another beast. On small data sets, cursors will often be faster than a sub-query.
  2. The larger the data set, the faster the cross-join will be against the cursors, with the threshold toward the middle size of the data set.
  3. Cursors do NOT scale. Lets see the performance of cursors on 10 million rows.