SQL Clone
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 Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98234 Visits: 38996
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
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

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

Paul DB
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98234 Visits: 38996
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
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36196 Visits: 9518
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 Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98234 Visits: 38996
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
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5096 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
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30915 Visits: 8986
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


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont 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 Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98234 Visits: 38996
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 Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222078 Visits: 42003
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36196 Visits: 9518
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