Help Solving Running Total Problem

Question

Help Solving Running Total Problem

Viewing 9 posts - 31 through 38 (of 38 total)

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

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 1

Lynn Pettis (4/21/2009)
I'm not sure why it isn't working for you. I run the above code as is and it works correctly with the test data.
Can you post the DDL and code you are trying to run?

Yes, this code works for my sample data, but as much as I try, I can't get it to work for my real data.

I know I'm probably doing something wrong, and I really wish I could post my live code.

I'm going to look at it again in the morning and see if I come up with anything.

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 2

Bevan keighley (4/21/2009)
Hi Goldie,
Do you have an index on UserID?

Yes, Non-Unique, Non-Clustered.

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

The only change to my code that I could suggest is 1) make sure you have declared the clustered index properly, and 2) change the index hint from 0 to 1.

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

Goldie Graber (4/21/2009)
Bevan keighley (4/21/2009)
Hi Goldie,
Do you have an index on UserID?
Yes, Non-Unique, Non-Clustered.

That is the problem. For the "quirky update" to work you need a clustered index on the columns you are doing the ordered update: CLUSTERED (UserID, CheckInID)

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 5

Lynn Pettis (4/21/2009)
Goldie Graber (4/21/2009)
Bevan keighley (4/21/2009)
Hi Goldie,
Do you have an index on UserID?
Yes, Non-Unique, Non-Clustered.
That is the problem. For the "quirky update" to work you need a clustered index on the columns you are doing the ordered update: CLUSTERED (UserID, CheckInID)

I did create a clustered index like that on my temp table. I thought Bevan was talking about the original table.

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 6

RBarryYoung (4/21/2009)
Goldie Graber (4/21/2009)
Also, Barry, would you be able to explain your query?
Sorry Goldie, not trying to be evasive but my brain isn't at 100% right now and this technique is hard to explain.
Here is an article where it is used and explained by Itzik Ben-Gan: http://www.sqlmag.com/Article/ArticleID/93606/sql_server_93606.html
Here's another thread where it is used and Jeff and I talk about it. http://www.sqlservercentral.com/Forums/Topic646593-338-1.aspx

Thanks for the links!

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

Goldie Graber (4/22/2009)
Lynn Pettis (4/21/2009)
Goldie Graber (4/21/2009)
Bevan keighley (4/21/2009)
Hi Goldie,
Do you have an index on UserID?
Yes, Non-Unique, Non-Clustered.
That is the problem. For the "quirky update" to work you need a clustered index on the columns you are doing the ordered update: CLUSTERED (UserID, CheckInID)
I did create a clustered index like that on my temp table. I thought Bevan was talking about the original table.

Then I'd need to see the code you are running against your temp table and the ddl for your temp table (including indexes) to see why my code isn't working against it.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Goldie Graber (4/20/2009)
As a post script, I ended up using Bevan's method.
It works well on tables with up to a few million records. Takes a minute or two.
When I had to run it on a table of 22 million it took over an hour!!
We will need to run this code in our production environment every once in a while, so I would appreciate if someone could help tweak this code or come up with a different approach.
On a different note, has anyone had trouble using Jeff's running total method before?
It seems to fail on tables with a few million records.

Is the table partitioned in any way?

--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: 1004363 More actions · Answer 9

Heh.... never mind... I didn't read down far enough.

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