StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL running sum for an MVC application
text
Body
copied!<p>I need a faster method to calculate and display a running sum.</p> <p>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.)</p> <p>The query looks like this:</p> <pre><code>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 </code></pre> <p>"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.</p> <p><b>Solution:</b> (one of many, this one is orders of magnitude faster than a sub-query)</p> <p>Create a new table with all the columns in the view <i>except for the <code>RunningTotal</code> col</i>. Create a stored procedure that first truncates the table, then <code>INSERT INTO</code> the table using <code>SELECT</code> all columns, without the running sum column.</p> <p>Use update local variable method:</p> <pre><code>DECLARE @Amount DECIMAL(18,4) SET @Amount = 0 UPDATE TABLE_YOU_JUST_CREATED SET RunningTotal = @Amount, @Amount = @Amount + ISNULL(Amount,0) </code></pre> <p>Create a task agent that will run the stored procedure once a day. Use the <code>TABLE_YOU_JUST_CREATED</code> for all your reports.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload