May 4, 2010 at 11:02 am
Nice article Solomon, BTW. 🙂
May 4, 2010 at 2:58 pm
Interesting solution.
Thanks for the article.
I also like the additional points of Interest that Paul has raised. They bring a good amount of education as well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 5, 2010 at 12:14 am
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)
Will we get the performance as good as the case when we didn't have any partition ?
May 5, 2010 at 1:41 am
Hi,
I found this article interesting, since partitioning is not a subject I have much experience with. I see that the more experienced users have found some errors/shortcomings. But for a "partitioning beginner" like me, you seem to have highlighted some interesting points.
It seems as if PartitionID is crucial when it comes to the performance of the query. One thing struck me: 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
It might be that it's just as expensive as the other examples in your article?
..Håvard..
May 5, 2010 at 2:20 am
Yes, this is the good old and tiring issue in Sqlserver. In oracle, partitions are just faster in all scenarios, however, this is not the case with sqlserver. It does not improve performance, but actually bring more penalties than improvements in my scenarios.
Somewhere I read that the 2008 version has some optimizations on the query side over partitions. I cannot remember where but I would say it was on the release notes. Unfortunately I am working in environments where everything is sql2005.
Has anyone tried if this is still an issue on sql2008?
Best regards,
Francisco Isla.
May 5, 2010 at 4:31 am
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
May 5, 2010 at 4:42 am
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 😉
Thanks for the link!
Francisco.
May 5, 2010 at 5:27 am
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# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
May 5, 2010 at 5:34 am
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# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
May 5, 2010 at 8:57 am
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.
May 5, 2010 at 9:43 am
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# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
May 5, 2010 at 9:51 am
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 on googles mail service
May 5, 2010 at 12:52 pm
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.
May 5, 2010 at 1:16 pm
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 . . . :angry:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 5, 2010 at 1:19 pm
SQL Guru,
I suspect I know what you are referring to. 😀
Tim
SQL Server MVP
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply