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

Improving Performance of Cross-Partition Queries Expand / Collapse
Author
Message
Posted Wednesday, May 5, 2010 4:31 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 4:18 PM
Points: 9,928, Visits: 11,206
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...

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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #915931
Posted Wednesday, May 5, 2010 4:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:19 AM
Points: 85, Visits: 160
Paul White NZ (5/5/2010)
Just faster in all scenarios eh? Sounds like magic! I wish SQL Server was Oracle...


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 ;)

Thanks for the link!

Francisco.
Post #915935
Posted Wednesday, May 5, 2010 5:27 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, December 14, 2014 10:06 PM
Points: 379, Visits: 1,991
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.

Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #915952
Posted Wednesday, May 5, 2010 5:34 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, December 14, 2014 10:06 PM
Points: 379, Visits: 1,991
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, .

Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #915956
Posted Wednesday, May 5, 2010 8:57 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 4:18 PM
Points: 9,928, Visits: 11,206
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #916157
Posted Wednesday, May 5, 2010 9:43 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, December 14, 2014 10:06 PM
Points: 379, Visits: 1,991
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/
Post #916205
Posted Wednesday, May 5, 2010 9:51 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:31 AM
Points: 4,472, Visits: 6,402
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, .

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 at GMail
Post #916219
Posted Wednesday, May 5, 2010 12:52 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 4:18 PM
Points: 9,928, Visits: 11,206
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #916370
Posted Wednesday, May 5, 2010 1:16 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:31 AM
Points: 4,472, Visits: 6,402
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!! :) But they are too busy building stuff that no one (or maybe 0.03% of installed base) will EVER use . . . sigh . . .


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #916393
Posted Wednesday, May 5, 2010 1:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 5, 2014 10:25 AM
Points: 49, Visits: 269
SQL Guru,
I suspect I know what you are referring to.

Tim
SQL Server MVP
Post #916395
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse