Running Totals

Question

Running Totals

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

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

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

wizard5203 (11/6/2009)
Jeff, thanks for the reply. You could tell from the post I'm a newbie. I looked at your link and will follow it on the next post. Thanks again.

Heh... thanks for the feedback but, just so you know... the reason why I didn't jump right in and help is because you didn't take the 5 minutes out to do it on THIS post. 😉

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Jeff Moden (11/6/2009)
Lynn Pettis (11/6/2009)
Jeff Moden (11/6/2009)
...
What? No comments on the sample code I provided? I really thought you'd have more to add. 😉
I'm not done posting on this thread yet... 😉

Good. I'm looking forward to further enlightenment as I'm sure you will bring some of your article into this.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

Lynn Pettis (11/6/2009)
First, watch SSC for Jeff's rewritten article on Running Totals. IIRC, it is coming out on 11/10/2009. It will be extremely enlightening.
With that, here is a another solution (albiet missing aspects that I'm sure Jeff will elaborate on):

First, thanks for the compliments, Lynn. Good way to end a long week.

You are correct in the post above and in the posts that follow that. The use of WITH (TABLOCKX) is pretty much essential unless you're working on a local Temp Table. Even then, the hint will keep the code from spawning a bazillion row locks that get escalated to page locks, then extent locks, and finally a table lock.

One of the things that absolutely must be prevented is parallelism... The OPTION(MAXDOP 1) hint prevents parallelism. Contrary to my previous posts were I tried to satiate nay-sayers and like the code you posted, no index hints are required.

Also, your good code example tests for the "break" condition first... you can get a tiny bit more speed if you test the "continue" condition first to short circuit the CASE statement. Dunno about the speed because my computer at home wigged out and I haven't reloaded SQL Server yet.

With all of that in mind, I'd recommend that the update look like this using your format on the CASE ...

UPDATE dbo.EmpTime

SET @bal = emprunningtime = CASE WHEN @empid = et.empid THEN @bal ELSE 0.0 END + et.emptime,

@empid = et.empid

FROM dbo.EmpTime et WITH(TABLOCKX)

OPTION (MAXDOP 1)

--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)

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

Lynn Pettis (11/6/2009)
Jeff Moden (11/6/2009)
Lynn Pettis (11/6/2009)
Jeff Moden (11/6/2009)
...
What? No comments on the sample code I provided? I really thought you'd have more to add. 😉
I'm not done posting on this thread yet... 😉
Good. I'm looking forward to further enlightenment as I'm sure you will bring some of your article into this.

What? No comments on the comments and sample code I provided? I really thought you'd have more to add. 😉

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Jeff Moden (11/8/2009)
Lynn Pettis (11/6/2009)
Jeff Moden (11/6/2009)
Lynn Pettis (11/6/2009)
Jeff Moden (11/6/2009)
...
What? No comments on the sample code I provided? I really thought you'd have more to add. 😉
I'm not done posting on this thread yet... 😉
Good. I'm looking forward to further enlightenment as I'm sure you will bring some of your article into this.
What? No comments on the comments and sample code I provided? I really thought you'd have more to add. 😉

Weekend, busy, hadn't had time to read between AFA Hockey, final games for AYSO Soccer, studying so I can officiate indoor soccer and make some extra cash (which is really needed).

I liked your comments. Yep, should have put in the OPTION (MAXDOP 1) to protect against parallelism, but it slipped my mind on the very short sample dataset. Would have clobbered me on a million row test dataset at work most likely.

Liked the further explaination of the reason for the TABLOCKX. Makes more sense now. Of course you probably explained in your original article and it too just slipped the memory buffers.

Interesting that the index hint isn't needed. Do you explain that in more detail in your article, or are you just going to make we wait until the 10th?

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

Man, I missed this one. Sorry for the delayed answer.

The only time you need the WITH(INDEX(0)) index hint is if you're going to do something whacky like Hugo did in the discussions that followed in the article... in which case you might have a bad design to begin with and should probably copy the necessary data to a Temp table and work on it there.

--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)