SQL running sum for an MVC application

Question

I need a faster method to calculate and display a running sum.

It's an MVC telerik grid that queries a view that generates a running sum using a sub-query. The query takes 73 seconds to complete, which is unacceptable. (Every time the user hits "Refresh Forecast Sheet", it takes 73 seconds to re-populate the grid.)

The query looks like this:

SELECT outside.EffectiveDate
[omitted for clarity]
       ,(
              SELECT SUM(b.Amount)
              FROM vCI_UNIONALL inside
              WHERE inside.EffectiveDate <= outside.EffectiveDate
              ) AS RunningBalance
[omitted for clarity]
FROM vCI_UNIONALL outside

"EffectiveDate" on certain items can change all the time... New items can get added, etc. I certainly need something that can calculate the running sum on the fly (when the Refresh button is hit). Stored proc or another View...? Please advise.

Solution: (one of many, this one is orders of magnitude faster than a sub-query)

Create a new table with all the columns in the view except for the RunningTotal col. Create a stored procedure that first truncates the table, then INSERT INTO the table using SELECT all columns, without the running sum column.

Use update local variable method:

DECLARE @Amount DECIMAL(18,4)
SET @Amount = 0
UPDATE TABLE_YOU_JUST_CREATED SET RunningTotal = @Amount, @Amount = @Amount + ISNULL(Amount,0)

Create a task agent that will run the stored procedure once a day. Use the TABLE_YOU_JUST_CREATED for all your reports.

Answer 1

Take a look at this post Calculate a Running Total in SQL Server

If you have SQL Server Denali, you can use new windowed function.

In SQL Server 2008 R2 I suggest you to use recursive common table expression. Small problem in CTE is that for fast query you have to have identity column without gaps (1, 2, 3,...) and if you don't have such a column you have to create a temporary or variable table with such a column and to move you your data there.

CTE approach will be something like this

declare @Temp_Numbers (RowNum int, Amount <your type>, EffectiveDate datetime)

insert into @Temp_Numbers (RowNum, Amount, EffectiveDate)
select row_number() over (order by EffectiveDate), Amount, EffectiveDate
from vCI_UNIONALL

-- you can also use identity
-- declare @Temp_Numbers (RowNum int identity(1, 1), Amount <your type>, EffectiveDate datetime)
-- insert into @Temp_Numbers (Amount, EffectiveDate)
-- select Amount, EffectiveDate
-- from vCI_UNIONALL
-- order by EffectiveDate

;with 
CTE_RunningTotal
as
(
    select T.RowNum, T.EffectiveDate, T.Amount as Total_Amount
    from @Temp_Numbers as T
    where T.RowNum = 1
    union all
    select T.RowNum, T.EffectiveDate, T.Amount + C.Total_Amount as Total_Amount
    from CTE_RunningTotal as C
        inner join @Temp_Numbers as T on T.RowNum = C.RowNum + 1
)
select C.RowNum, C.EffectiveDate, C.Total_Amount
from CTE_RunningTotal as C
option (maxrecursion 0)

There're may be some questions with duplicates EffectiveDate values, it depends on how you want to work with them - do you want to them to be ordered arbitrarily or do you want them to have equal Amount?