SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Improving Performance of Cross-Partition Queries


Improving Performance of Cross-Partition Queries

Author
Message
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35366 Visits: 11361
hardwaremister (5/5/2010)
Yes, this is the good old and tiring issue in Sqlserver. In oracle, partitions are just faster in all scenarios...

Just faster in all scenarios eh? Sounds like magic! I wish SQL Server was Oracle...Laugh

Yes, partitioning performance is much improved in 2008 in many circumstances. To be fair, the 2005 partitioning design was *all* about making maintenance easier - not performance.

One document that explains the improvements well can be found on TechNet:
http://technet.microsoft.com/en-us/magazine/2008.04.dwperformance.aspx

The whole document is good, but the Partitioned Table Parallelism section is most relevant to your question.

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
hardwaremister
hardwaremister
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 164
Paul White NZ (5/5/2010)
Just faster in all scenarios eh? Sounds like magic! I wish SQL Server was Oracle...Laugh


Hehehe, I never meant to say *all* scenarios, but yes, table partitioning in sql2005 is not usable for its most desirable functionality. On the other hand, I do not miss oracle at all Wink

Thanks for the link!

Francisco.
Solomon Rutzky
Solomon Rutzky
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3324 Visits: 3029
matlani.dhiraj (5/5/2010)

How about this query ?

SELECT MIN(etp.IntDate)
FROM dbo.ExampleTablePartitioned etp
WHERE etp.PartitionID in (1,2,3,4,5,6,7,8,9,10)


Hello Matlani. Unfortunately, using an IN list has the same performance problem. The query still takes about 10 seconds each time using the IN (1, 2, 3, ...) list. Sad

Take care,
Solomon...

SQL# - http://www.SQLsharp.com/
Solomon Rutzky
Solomon Rutzky
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3324 Visits: 3029
aeternam (5/5/2010)
Using a while loop in combination with a table variable shouldn't, in general, be the best performing option.

How would this query perform:
SELECT <something>
FROM <the partitioned table>
WHERE etp.PartitionID <= 10

..Håvard..


Hello Håvard. Yes, the WHILE loop shouldn't be the best performing option, but sadly it is (again outside of the non-partitioned index; but which solution is best for you depends on your situation). I tried your idea of using PartitionID <= 10 and it had the same effect as using the IN list as well as not using any WHERE condition at all: they all take 10 - 11 seconds for each run. Again, Sad.

Take care,
Solomon...

SQL# - http://www.SQLsharp.com/
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35366 Visits: 11361
Solomon Rutzky (5/5/2010)
...the WHILE loop shouldn't be the best performing option, but sadly it is.

Are you sure Solomon? I would expect the APPLY + $PARTITION approach to be faster (see code posted earlier) as well as being more flexible (it dynamically accounts for extra/fewer partitions) and set-based.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Solomon Rutzky
Solomon Rutzky
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3324 Visits: 3029
Paul White NZ (5/5/2010)
Solomon Rutzky (5/5/2010)
...the WHILE loop shouldn't be the best performing option, but sadly it is.

Are you sure Solomon? I would expect the APPLY + $PARTITION approach to be faster (see code posted earlier) as well as being more flexible (it dynamically accounts for extra/fewer partitions) and set-based.


Hey Paul. Actually, I had not had time to test the CROSS APPLY approach and was answering in terms of my solution against the simple WHERE clause solutions. I did just test your example and the result was rather interesting in that it depends on which metric you go by.

If you go by query cost (based on Actual Execution Plan), then the CROSS APPLY + $PARTITION is slightly faster coming in at 0.0387787 as opposed to my solution which has a total cost of 0.1361422 (which is not a huge difference but still one definitely costs less).

If you go by STATISTICS IO and STATISTICS TIME, then my solution is slightly faster. Here is the output for the CROSS APPLY + $PARTITION query:

Table 'ExampleTable'. Scan count 11, logical reads 48, physical reads 38, read-ahead reads 0.
Table 'sysrowsets'. Scan count 1, logical reads 35, physical reads 0, read-ahead reads 33.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 340 ms.

Whereas my approach shows an aggregate "elapsed time" of just 8 ms and "cpu time" of 0 ms while IO stats show aggregates of 11 Scans, 59 Logical reads, 0 Physical reads, and 0 Read-ahead reads.


Now, as far as dynamic goes, the solution I presented here was simplified for the example. In our systems where I work we have a dynamic approach to get the highest PartitionID since we store those along with the CustomerIDs. However, I do appreciate that your approach (and maybe also that of Ben-Gan) is dynamic in more situations than mine since my approach assumes a non-sparse list of numbers (or something sequential). For my purposes that is all it will ever be but it is good for people to have another approach if their data is not as predictable (or in my situation if we skipped PartitionID 11 and went to 12--which we won't do but that would expose the issue).

As far as set-based approaches go, yes, the CROSS APPLY + $PARTITION approach is more ideal.

Take care,
Solomon...

[edited to add the following:]
PS, for the sake of being thorough, I added in the dynamic MAX(PartitionID) lookup that we use since that would more so equate to the fully dynamic approach of using CROSS APPLY + $PARTITION. That added 1 Scan and 24 logical reads to my approach. The new total is 12 Scans and 83 logical reads which is the same as the CROSS APPLY + $PARTITION approach.

And just to be more thorough, I then tested this in another environment and the STATISTICS showed no physical reads for the CROSS APPLY + $PARTITION approach and an elapsed time of 1 ms. So now they appear to be equal.

SQL# - http://www.SQLsharp.com/
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32077 Visits: 8672
Solomon Rutzky (5/5/2010)
aeternam (5/5/2010)
Using a while loop in combination with a table variable shouldn't, in general, be the best performing option.

How would this query perform:
SELECT <something>
FROM <the partitioned table>
WHERE etp.PartitionID <= 10

..Håvard..


Hello Håvard. Yes, the WHILE loop shouldn't be the best performing option, but sadly it is (again outside of the non-partitioned index; but which solution is best for you depends on your situation). I tried your idea of using PartitionID <= 10 and it had the same effect as using the IN list as well as not using any WHERE condition at all: they all take 10 - 11 seconds for each run. Again, Sad.

Take care,
Solomon...


Wouldn't APPLY also force a nested loop iteration over each partition and thus also be slow?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35366 Visits: 11361
TheSQLGuru (5/5/2010)
Wouldn't APPLY also force a nested loop iteration over each partition and thus also be slow?

Yes to the loop join - but that's exactly what is required ;-)
Fetching the top or bottom one row per partition is the name of the game here.
Given a straight choice between a WHILE loop and a correlated loop join... ;-)
It would be hard to show a real performance difference over a small number of iterations (like the 10 here), but my point is simply that a set-based solution (correlated loop join or no) is a better design.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32077 Visits: 8672
Paul White NZ (5/5/2010)
TheSQLGuru (5/5/2010)
Wouldn't APPLY also force a nested loop iteration over each partition and thus also be slow?

Yes to the loop join - but that's exactly what is required ;-)
Fetching the top or bottom one row per partition is the name of the game here.
Given a straight choice between a WHILE loop and a correlated loop join... ;-)
It would be hard to show a real performance difference over a small number of iterations (like the 10 here), but my point is simply that a set-based solution (correlated loop join or no) is a better design.


Well, the BEST answer is for MS to fix the damn optimizer to work properly with partitions in ALL ASPECTS!! Smile But they are too busy building stuff that no one (or maybe 0.03% of installed base) will EVER use . . . sigh . . . Angry

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
chapman.tim
chapman.tim
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 332
SQL Guru,
I suspect I know what you are referring to. :-D

Tim
SQL Server MVP
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