Running Total

Question

Post reply

Running Total

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 1

Thanks for the heads up Jeff, how would you tackle it then ?

Hope this helps...

Ford Fairlane
Rock and Roll Detective

x SSC-Insane Points: 23660 More actions · Answer 2

Awesome link here discussing various methods:

http://stackoverflow.com/questions/11310877/calculate-running-total-running-balance

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Ford Fairlane (1/19/2014)
Thanks for the heads up Jeff, how would you tackle it then ?

Start by reducing the noise to see what's going on:

SELECT

x.YearMonth,

[DEPTH] = (

SELECT

SUM(b.DEPTH)

FROM HOLELOCATION b

CROSS APPLY (SELECT [YearMonth] = CONVERT(CHAR(6),CAST(b.ENDDATE AS DATETIME), 112)) x2

WHERE b.HOLEID LIKE 'RHRC%'

AND x2.YearMonth <= x.YearMonth

)

FROM HOLELOCATION a

CROSS APPLY (SELECT [YearMonth] = CONVERT(CHAR(6),CAST(a.ENDDATE AS DATETIME), 112)) x

WHERE a.HOLEID LIKE 'RHRC%'

AND x.YearMonth IS NOT NULL

GROUP BY x.YearMonth

ORDER BY x.YearMonth

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 4

Ford Fairlane (1/19/2014)
Thanks for the heads up Jeff, how would you tackle it then ?

I use the Quirky Update method and will probably continue to do so even after my company upgrades to 2012 and then to 2014. It's not for the faint of heart and there are a shedload of rules to follow but, for the size of the tables I used it for, it was worth it. It'll do a running total on a million row table (comparatively small nowadays) in just a couple of seconds even if you use the more complicated version with built in "sort order verification".

As an alternative, if the table that you need running totals for is a WORM table (write once, read many... like a check book where corrections are added in as additional transactions) AND you don't mind storing the running total, it might be better to use a trigger or some other code to calculate the running totals just for the new rows based on the previous maximum record.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 5

Thanks Jeff, appreciate the advice.

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Alan Burstein SSC Guru Points: 61136 More actions · Answer 6

patrickmcginnis59 10839 (1/21/2014)
Awesome link here discussing various methods:
http://stackoverflow.com/questions/11310877/calculate-running-total-running-balance

This article provides different ways to do a running total but has some might not be the best advice based on my experience. First, "the recommendations, in order of preference" are:

1. SUM() OVER() ... ROWS, if on 2012

2. CLR method, if possible

3. First recursive CTE method, if possible

4. Cursor

5. The other recursive CTE methods

6. Quirky update

7. Join and/or correlated subquery

Even in 2012 the SUM() OVER() technique is not the fastest. CLR is also not the fastest and is unnecessary IMHO for this task. Also, I think (no time to test this) that the Cursor method has an issue.

DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY

could be optimized like this:

DECLARE c CURSOR FAST_FORWARD FOR

That said, cursors are bad (except perhaps when used for their original purpose which is NOT running totals); there are so many better ways that a cursor need not exist in that list of choices.

It's been recommended already but is worth repeating, this article: Solving the Running Total and Ordinal Rank Problems[/url] is a great start for this topic.

I also highly recommend Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions by Itzek Ben Gan which includes 12 great pages on the subject.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

x SSC-Insane Points: 23660 More actions · Answer 7

Alan.B (1/24/2014)
That said, cursors are bad (except perhaps when used for their original purpose which is NOT running totals); there are so many better ways that a cursor need not exist in that list of choices.

I think the point was made in that article that the cursor solution was chosen over the quirky update because of the somewhat unsupported nature of the quirky update, but that the cursor method loses on pure performance.