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 Tuesday, January 27, 2009 6:07 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 21, 2013 4:37 PM
Points: 701, Visits: 211
I don't get this. Both of the solutions create a non-partitioned instance of the partitioned table. One form is a temp table, and the other form is a materialized view (by the clustered index). This seems to me that it defeats the purpose of partitioning the table in the first place.


Post #644613
Posted Tuesday, January 27, 2009 6:41 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 22,499, Visits: 30,205
YeshuaAgapao (1/27/2009)
I don't get this. Both of the solutions create a non-partitioned instance of the partitioned table. One form is a temp table, and the other form is a materialized view (by the clustered index). This seems to me that it defeats the purpose of partitioning the table in the first place.


The purpose was to find a method for updating a partitioned table with running balances. The highly performant method discussed in Jeff Moden's article doesn't work on partitioned tables. So, how do you accomplish this task if you need to do it? The article shows you two methods for accomplishing this task.

Behind all this, is a principle, how do you accomplish any task involving partitioned tables that fails, but works fine when done with a non-partitioned (or standard) table. The article wasn't discussing the pro's and con's of storing running totals in the database vs computing them on the client-side, or the pro's and con's of partitioned tables.

I hope this explanation helps you with understanding the idea behind the article.



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 #644623
Posted Tuesday, January 27, 2009 7:54 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
I guess I should first say thank you for the wonderful acknowledgements. Thanks, Lynn.

It's also a real pleasure to read an article with a logical progression and a whole lot of "Alice's Restaurant" code examples to play with especially when they're that readable. Very nicely done, Mr. Pettis. :)


--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." -- 04 August 2013
(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 #644638
Posted Tuesday, January 27, 2009 8:03 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 22,499, Visits: 30,205
And I must thank you in return, Jeff. If it hadn't been for you, and some of the other gurus on SSC, I never would have written this article. Your article was the basis of it, and your tutelage the catalyst.

I also appreciate your having taken the time to read my initial draft of the article. Your comments and suggestions, I feel, helped the article as well.

So, I have to give you a well done as well.



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 #644639
Posted Tuesday, January 27, 2009 8:57 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:34 AM
Points: 2,278, Visits: 3,046
Lynn Pettis (1/27/2009)
Smendle (1/27/2009)
That can happen on multi-processor systems that SQL is allowed to use.


I am sure this is in response to the following:

I received a private message with a question regarding my article that I can't answer, so I thought I'd put it here with the hopes that some of the gurus (Jeff, Gail, Grant, Gus, et al) out there may be able to help out.


Why is the CPU time greater than the elapsed time

CPU time = 5063 ms, elapsed time = 3309 ms.



Thinking about it, that actually makes sense and isn't something I'd have come up with on my own, thanks. Do any of the other gurus out there also agree?


The answer to this question is parallelism. The cpu time is greater because the query is spread across multiple processors, which allows you to do a lot more cpu processing in a shorter period of time. This is how CPU time becomes greater than elapsed time. If you set max dop to 1, you will not see the same behavior.




My blog: http://jahaines.blogspot.com
Post #644658
Posted Tuesday, January 27, 2009 9:05 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:34 AM
Points: 2,278, Visits: 3,046
Forgot to comment on the article .

I thought the article was very informative, easy to read/understand, and offered great alternative methods to solving the "running total" enigma.

Nicely done.




My blog: http://jahaines.blogspot.com
Post #644661
Posted Tuesday, January 27, 2009 9:32 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 22,499, Visits: 30,205
Adam Haines (1/27/2009)
Forgot to comment on the article .

I thought the article was very informative, easy to read/understand, and offered great alternative methods to solving the "running total" enigma.

Nicely done.


Thank you, I appreciate your kind words regarding the article.



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 #644668
Posted Wednesday, January 28, 2009 9:37 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 21, 2013 4:37 PM
Points: 701, Visits: 211
Makes sense now. I would still suggest that if the running totals requirement was the dominant form of querying on the table, I would rather just un-partition the whole table rather than create a non-partitioned duplicate of it (temporarily with the temp table or worse, permanently with the non-paritioned indexed view). Otherwise I would really try not to fufill any kind of real-time reporting requirement on it, only batch pre-generation of the aggregates.


Post #645059
Posted Wednesday, January 28, 2009 10:12 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 22,499, Visits: 30,205
YeshuaAgapao (1/28/2009)
Makes sense now. I would still suggest that if the running totals requirement was the dominant form of querying on the table, I would rather just un-partition the whole table rather than create a non-partitioned duplicate of it (temporarily with the temp table or worse, permanently with the non-paritioned indexed view). Otherwise I would really try not to fulfill any kind of realtime reporting requirement on it, only batch pre-generation of the aggregates.


I can't agree with un-partitioning the table. There may be other reasons that the table is partitioned. One reason, perhaps it isn't one million rows of data over ten years but 1 billion rows of data over ten years. With that, the majority of the queries against the table are limited to the current year and maybe the previous year with less than five percent of all queries access data older than that, but the data for all ten years needs to be available based on corporate policies.

Please remember, the actual purpose of this article wasn't just to show you how to compute running totals on partitioned tables. The purpose was to demonstrate a methodology that could possibly be used to address problems with updates to partitioned tables. Can it be used in all cases, maybe not, but it is a tool that is available.

Two other items. If you were to use the interim table method, you actually would only create a table with the necessary columns needed to complete the update. If the source table has 25 columns and is 2500 bytes wide, but you only need 6 columns and 75 bytes, that is all you would use. Same idea with the indexed view. You would only build the view using the columns you needed to perform the update.




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 #645095
Posted Wednesday, January 28, 2009 10:23 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 21, 2013 4:37 PM
Points: 701, Visits: 211
Yeah if you could restrict the rows (and columns if savings is alot) it would be more reasonable. None of your code had WHERE clauses so I assumed that you wanted to crunch the whole table. If there was a WHERE clause, does Jeff's version break even if the qualifying rows don't span partitions?


Post #645106
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse