KEY LEARNING: If you are using SQL SERVER 2012 or higher, when calculating running total values or variations, use Windowing Aggregate Functions as they are much much more performant than other methods.
From time to time I run across running total style problems when working with Sage 500. These include showing the Customers running total across invoices and credit memos or applying unapplied payments across open invoices, using the earliest transaction date. One interesting variation is the allocation of on hand inventory to sales orders, by the sales order date, ensuring that the earliest sales order will be shipped first.
Prior to 2012, running total problems could be solved with a number of different approaches:
- Correlated Sub Query
- Cursor
- Quirky Update
- Row Based Loop
I had tended to go to the Quirky Update approach as this seemed to be the most performant. However I have stopped using that approach because I encountered in instance on a poorly performing SQL Server where the method was non-deterministic.
That has left me with more row by row approaches which always make me think there has got to be something better.
Step in the Microsoft SQL Server team, who extended windowing functions and released them in SQL Server 2012. You’ll be familiar with other windowing functions like Row_Number, DENSE_RANK, RANK, NTILE using the OVER (PARTION BY). In SQL Server 2012, they aggregate window functions, which allow you to specify a partition window, and what rows in the partition to include in the aggregate. For running totals you would specify the first row in the window and all the rows up to the current row.
I wanted to compare the performance of a cursor based inventory allocation to a windows function based allocation. The difference was significant. On a database with 59,171 open sales order lines, the cursor based script took 138 seconds to run. The comparable windowing function took 2 seconds to run.
An article by Aaron Bertrand does a similar study, but uses 7 different methods for calculating running totals.
As soon as you can move to SQL 2012, put the windowing method into practice where appropriate. Your users will see significant improvement where they come into play.
Here is my example code for both the windows function and the cursor based queries.
/*
WINDOWING BASED SOLUTION
*/
DECLARE @StartDateTime DATETIME;
DECLARE @EndDateTime DATETIME;
SET @StartDateTime = GETDATE();
SELECT runtot.WhseKey, runtot.ItemKey, runtot.CreateDate, runtot.SOLineKey, runtot.QtyOpenToShip, runtot.RunningTotal, qoh.QtyOnHand,
QtyAllocated = CASE
WHEN qoh.QtyOnHand >= runtot.RunningTotal THEN runtot.QtyOpenToShip
WHEN qoh.QtyOnHand - runtot.RunningTotal + runtot.QtyOpenToShip >= 0 THEN qoh.QtyOnHand - runtot.RunningTotal + runtot.QtyOpenToShip
ELSE 0 END
FROM (
SELECT sold.WhseKey, sol.ItemKey, so.CreateDate, sol.SOLineKey, sold.QtyOpenToShip,
SUM(QtyOpenToShip) OVER (PARTITION BY sold.WhseKey, sol.ItemKey ORDER BY sold.WhseKey, sol.ItemKey, so.CreateDate RANGE UNBOUNDED PRECEDING) [RunningTotal]
FROM tsoSalesOrder so WITH (NOLOCK)
JOIN tsoSOLine sol WITH (NOLOCK)
ON so.SOKey = sol.SOKey
JOIN tsoSOLineDist sold WITH (NOLOCK)
ON sol.SOLineKey = sold.SOLineKey
WHERE sold.WhseKey IS NOT NULL
AND sold.QtyOpenToShip > 0
AND so.Status = 1 --Open
AND sol.Status = 1 --Open
) runtot
JOIN (
SELECT wb.WhseKey, wbi.ItemKey, SUM(wbi.QtyOnHand) QtyOnHand
FROM timWhseBinInvt wbi WITH (NOLOCK)
JOIN timWhseBin wb WITH (NOLOCK)
ON wbi.WhseBinKey = wb.WhseBinKey
GROUP BY wb.WhseKey, wbi.ItemKey
HAVING SUM(wbi.QtyOnHand) > 0
) qoh
ON runtot.WhseKey = qoh.WhseKey AND runtot.ItemKey = qoh.ItemKey
ORDER BY runtot.WhseKey, runtot.ItemKey, runtot.CreateDate, runtot.SOLineKey;
SET @EndDateTime = GETDATE();
SELECT DATEDIFF(MICROSECOND, @StartDateTime, @EndDateTime) [Duration_MCS];
/*
CURSOR BASED SOLUTION
*/
DECLARE @StartDateTime DATETIME;
DECLARE @EndDateTime DATETIME;
SET @StartDateTime = GETDATE();
IF OBJECT_ID('tempdb..#SOLineAllocations') IS NOT NULL DROP TABLE #SOLineAllocations;
DECLARE @WhseKey INT,
@ItemKey INT,
@QtyRemaining DECIMAL(16,8),
@SOLineKey INT,
@QtyOpenToShip DECIMAL(16,8),
@QtyOnHand DECIMAL(16,8),
@QtyAllocated DECIMAL(16,8),
@QtyAllocatedThisLine DECIMAL(16,8);
CREATE TABLE #SOLineAllocations
(
WhseKey INT NOT NULL,
ItemKey INT NOT NULL,
CreateDate DATETIME NOT NULL,
SOLineKey INT,
QtyOpenToShip DECIMAL(16,8) NOT NULL,
QtyAllocated DECIMAL(16,8) NOT NULL DEFAULT(0)
);
CREATE CLUSTERED INDEX idx_SOLineAllocations_WhseKey_ItemKey_CreateDate_SOLineKey ON #SOLineAllocations (WhseKey, ItemKey, CreateDate, SOLineKey);
INSERT INTO #SOLineAllocations (WhseKey, ItemKey, CreateDate, SOLineKey, QtyOpenToShip)
SELECT sold.WhseKey, sol.ItemKey, so.CreateDate, sol.SOLineKey, sold.QtyOpenToShip
FROM tsoSalesOrder so WITH (NOLOCK)
JOIN tsoSOLine sol WITH (NOLOCK)
ON so.SOKey = sol.SOKey
JOIN tsoSOLineDist sold WITH (NOLOCK)
ON sol.SOLineKey = sold.SOLineKey
WHERE WhseKey IS NOT NULL
AND QtyOpenToShip > 0
AND so.Status = 1 --Open
AND sol.Status = 1 --Open
ORDER BY sold.WhseKey, sol.ItemKey, so.CreateDate, sol.SOLineKey;
--Simplification for example
DECLARE qty_cursor CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT wb.WhseKey, wbi.ItemKey, SUM(wbi.QtyOnHand) QtyOnHand
FROM timWhseBinInvt wbi WITH (NOLOCK)
JOIN timWhseBin wb WITH (NOLOCK)
ON wbi.WhseBinKey = wb.WhseBinKey
JOIN (
SELECT DISTINCT WhseKey, ItemKey FROM #SOLineAllocations
) wrk
ON wb.WhseKey = wrk.WhseKey AND wbi.ItemKey = wrk.ItemKey
GROUP BY wb.WhseKey, wbi.ItemKey
HAVING SUM(wbi.QtyOnHand) > 0;
OPEN qty_cursor;
FETCH NEXT FROM qty_cursor INTO @WhseKey, @ItemKey, @QtyOnHand;
WHILE @@Fetch_Status = 0
BEGIN
SET @QtyAllocated = 0;
DECLARE alloc_cursor CURSOR
FOR
SELECT SOLineKey, QtyOpenToShip
FROM #SOLineAllocations
WHERE WhseKey = @WhseKey
AND ItemKey = @ItemKey
ORDER BY CreateDate;
OPEN alloc_cursor;
FETCH NEXT FROM alloc_cursor INTO @SOLineKey, @QtyOpenToShip;
WHILE @@Fetch_Status = 0
BEGIN
IF @QtyOnHand - (@QtyAllocated + @QtyOpenToShip) >= 0
SET @QtyAllocatedThisLine = @QtyOpenToShip
ELSE IF @QtyOnHand - @QtyAllocated >= 0
SET @QtyAllocatedThisLine = @QtyOnHand - @QtyAllocated
ELSE
SET @QtyAllocatedThisLine = 0
UPDATE #SOLineAllocations
SET QtyAllocated = @QtyAllocatedThisLine
WHERE SOLineKey = @SOLineKey
SET @QtyAllocated = @QtyAllocated + @QtyAllocatedThisLine
IF @QtyAllocatedThisLine <= 0
BREAK;
FETCH NEXT FROM alloc_cursor INTO @SOLineKey, @QtyOpenToShip;
END
CLOSE alloc_cursor;
DEALLOCATE alloc_cursor;
FETCH NEXT FROM qty_cursor INTO @WhseKey, @ItemKey, @QtyOnHand;
END
CLOSE qty_cursor;
DEALLOCATE qty_cursor;
SELECT * FROM #SOLineAllocations ORDER BY WhseKey, ItemKey, CreateDate;
SET @EndDateTime = GETDATE();
SELECT DATEDIFF(MICROSECOND, @StartDateTime, @EndDateTime) [Duration_MCS];
