Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Solving the “Running Totals” Problem in SQL Server 2005 with Partitioned Tables


Solving the “Running Totals” Problem in SQL Server 2005 with Partitioned Tables

Author
Message
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
I didn't test Jeff's code against just one partition, but from what I saw of the data when run against the whole table, I'd have to say that in that case the results may be what you would be looking for. This would be a good test for someone to try.

It may be a while before I can, as I have some other things going on right now outside of work that are taking up quite a bit of my free time.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Paul DB
Paul DB
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 258
Wooh ... this is some article. Great job! Cool

Paul DB
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
Paul DB (1/28/2009)
Wooh ... this is some article. Great job! Cool


Thank you. Blush

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
You know, I've been so busy the last month or so, I missed this when it first came out. This is an impressive piece of work, Lynn, congratulations. I am sure that I will be referring to it in the future.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
RBarryYoung (2/21/2009)
You know, I've been so busy the last month or so, I missed this when it first came out. This is an impressive piece of work, Lynn, congratulations. I am sure that I will be referring to it in the future.


Thank you. Blush

I do have to give credit to Jeff for the groundwork he laid in his article, and to Gail for the idea based on her comment on Jeff's work about the "quirky update" not working with partitioned tables. That was my incentive to find a viable solution.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
jcrawf02
jcrawf02
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1422 Visits: 19324
Grant Fritchey (1/27/2009)
Uh... Wow... Over-achieve much?



Ha! This coming from a guy who wrote 180 pages on execution plans.

And Lynn, I concur with all of the above who congratulated your article, very nice writing and explanation of the process.

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6976 Visits: 8839
As you suggested the "Order by" testing with Jeffs solution, I also tested your marvellous solution for partitioned objects.

First findings .... It no longer works .... SQL2008 ( sp1 ) is getting smarter.
The more things we try to fool it, it just ignores them.
They must have a huge pile of fools, to anticipate all the foolish things we try :-D (forcing a sets processing in a certain order.)

I'm still trying to figure out why ..... or why not.

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
ALZDBA (4/27/2009)
As you suggested the "Order by" testing with Jeffs solution, I also tested your marvellous solution for partitioned objects.

First findings .... It no longer works .... SQL2008 ( sp1 ) is getting smarter.
The more things we try to fool it, it just ignores them.
They must have a huge pile of fools, to anticipate all the foolish things we try :-D (forcing a sets processing in a certain order.)

I'm still trying to figure out why ..... or why not.





Okay, I really need to get SQL Server 2008 Developers Edition. Have you tested the ORDER BY on the partitioned table itself?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45127 Visits: 39923
Actually, it only looked like the ORDER BY method worked in my article. It wasn't the ORDER BY that made it work. Of course, I'm just a day or two from submitting the article for republishing and I don't want to give away too much thunder. :-)

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
Lynn Pettis (4/27/2009)
ALZDBA (4/27/2009)
As you suggested the "Order by" testing with Jeffs solution, I also tested your marvellous solution for partitioned objects.

First findings .... It no longer works .... SQL2008 ( sp1 ) is getting smarter.
The more things we try to fool it, it just ignores them.
They must have a huge pile of fools, to anticipate all the foolish things we try :-D (forcing a sets processing in a certain order.)

I'm still trying to figure out why ..... or why not.


Okay, I really need to get SQL Server 2008 Developers Edition. Have you tested the ORDER BY on the partitioned table itself?


Lynn, try here. $45 is pretty good, and because I'm an Amazon Prime customer, I get the 2-day shipping free.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search