What's the Most Efficient Way to Calculate Running Totals in SQL Server?

Detailed explanation of SQL Server cumulative sum calculation method

Calculating cumulative sums in SQL queries is a common requirement. The OVER clause provides a convenient way to perform such calculations in Oracle and ANSI-SQL. However, SQL Server's implementation of the OVER clause lacks the flexibility to handle certain use cases.

Update Tips

Despite its disadvantages, an effective technique for calculating cumulative sums in SQL Server is to use an aggregate set statement. This method includes:

1. Create a temporary table with the same columns as the original table.
2. Insert the data from the original table into the temporary table while setting the cumulative sum column to NULL.
3. Update temporary table to calculate cumulative sum based on previous values.

This technique is very efficient, but has potential problems:

The order in which the
• UPDATE statement processes rows may not always be the same as the date order.
• The update trick relies on undocumented implementation details of SQL Server.

Benchmark comparison

Benchmark testing shows that, within the constraints of SQL Server, the cursor method is the fastest and safest way to calculate a cumulative sum. The update trick provides the highest performance, but has potential issues with processing order. Therefore, for production code, it is recommended to use a cursor-based approach.

Example code and benchmark data

The following code provides a working example along with test data for benchmarking:

Test data settings:

<code class="language-sql">CREATE TABLE #t (
    ord INT PRIMARY KEY,
    total INT,
    running_total INT
);

SET NOCOUNT ON;
DECLARE @i INT;
SET @i = 0;
BEGIN TRAN;
WHILE @i < 10000
BEGIN
    INSERT INTO #t (ord, total) VALUES (@i, ABS(CHECKSUM(NEWID()) % 1000));
    SET @i = @i + 1;
END;
COMMIT TRAN;</code>

Test method:

Test 1: Correlated subquery

<code class="language-sql">SELECT ord,
       total,
       (SELECT SUM(total)
        FROM #t b
        WHERE b.ord <= a.ord) AS RunningTotal
FROM #t a
ORDER BY a.ord;</code>

Test 2: Cross-connection

<code class="language-sql">SELECT a.ord,
       a.total,
       SUM(b.total) AS RunningTotal
FROM #t a
CROSS JOIN #t b
WHERE b.ord <= a.ord
GROUP BY a.ord, a.total
ORDER BY a.ord;</code>

Test 3: Cursor

<code class="language-sql">DECLARE @TotalTable TABLE (
    ord INT PRIMARY KEY,
    total INT,
    running_total INT
);

DECLARE forward_cursor CURSOR FAST_FORWARD
FOR
SELECT ord,
       total
FROM #t
ORDER BY ord;

OPEN forward_cursor;

DECLARE @running_total INT,
        @ord INT,
        @total INT;

SET @running_total = 0;

FETCH NEXT FROM forward_cursor
INTO @ord,
      @total;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @running_total = @running_total + @total;
    INSERT @TotalTable VALUES (@ord,
                              @total,
                              @running_total);
    FETCH NEXT FROM forward_cursor
    INTO @ord,
          @total;
END;

CLOSE forward_cursor;
DEALLOCATE forward_cursor;

SELECT *
FROM @TotalTable;</code>

Test 4: Update Tips

<code class="language-sql">DECLARE @total INT;
SET @total = 0;
UPDATE #t
SET running_total = @total,
    @total = @total + total;

SELECT *
FROM #t;</code>

By comparing the execution efficiency of the above four methods, the best practice for calculating cumulative sums in SQL Server can be derived. Note that actual performance may vary depending on data volume and server configuration.

The above is the detailed content of What's the Most Efficient Way to Calculate Running Totals in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!