Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Solving the “Running Totals” Problem in SQL Server 2005 with Partitioned Tables Expand / Collapse
Author
Message
Posted Wednesday, January 28, 2009 10:30 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 20,744, Visits: 32,556
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.




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)
Post #645119
Posted Wednesday, January 28, 2009 10:53 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 9, 2013 1:26 PM
Points: 60, Visits: 258
Wooh ... this is some article. Great job!

Paul DB
Post #645147
Posted Wednesday, January 28, 2009 11:00 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 20,744, Visits: 32,556
Paul DB (1/28/2009)
Wooh ... this is some article. Great job!


Thank you.



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)
Post #645157
Posted Saturday, February 21, 2009 8:42 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 24, 2014 11:52 AM
Points: 9,294, Visits: 9,484
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."
Post #662128
Posted Saturday, February 21, 2009 9:41 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 20,744, Visits: 32,556
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.

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.



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)
Post #662135
Posted Wednesday, February 25, 2009 11:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 11:55 AM
Points: 1,330, Visits: 19,306
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."
Post #664511
Posted Monday, April 27, 2009 7:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:09 AM
Points: 6,735, Visits: 8,495
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 (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

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


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #704896
Posted Monday, April 27, 2009 7:48 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 20,744, Visits: 32,556
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 (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 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)
Post #704929
Posted Monday, April 27, 2009 7:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #705462
Posted Sunday, May 3, 2009 12:55 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 24, 2014 11:52 AM
Points: 9,294, Visits: 9,484
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 (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."
Post #709017
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse