August 14, 2012 at 6:10 am
Dear All,
How data will get search in partitioned table which is having index ?
Regards,
KoteRavindra
August 14, 2012 at 6:16 am
Could you elaborate more on the question please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2012 at 7:31 am
Assuming you are filtering on the column that you are partitioning against, partition elimination will ignore any partitions that do not contain the value that you are looking for. So the answer to your question, is "the remaining partitions that have not been eliminated by partition elimination will be queried for your value"
[font="Courier New"]Michael Connolly
[/font]
August 14, 2012 at 8:16 am
sqlharmony (8/14/2012)
Assuming you are filtering on the column that you are partitioning against, partition elimination will ignore any partitions that do not contain the value that you are looking for. So the answer to your question, is "the remaining partitions that have not been eliminated by partition elimination will be queried for your value"
Is that true in the case where the index is not aligned with the table? I think, as Gail mentioned, there is a lot more information required before a complete answer can be given...
Jared
CE - Microsoft
August 14, 2012 at 8:37 am
Yes, I agree more information is required with regards to the indexes on the table. I was just saying that when it comes to partitioning and trying to get the max performance out of querying on a partitioned table, querying on the column that the table is partitioned on will give the best performance results because of partition elimination.
[font="Courier New"]Michael Connolly
[/font]
August 14, 2012 at 8:48 am
sqlharmony (8/14/2012)
I was just saying that when it comes to partitioning and trying to get the max performance out of querying on a partitioned table, querying on the column that the table is partitioned on will give the best performance results because of partition elimination.
Except that partitioning doesn't often give performance improvement. If we compare a clustered index on a column and a partition function on that column (with partitioned clustered index), then partition elimination gives very little as the clustered index seek would already have eliminated most of the rows that aren't needed. If you're partitioning a heap with no indexes, sure, partition elimination will give great improvements.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2012 at 9:31 am
Agreed Gail, but assuming non clustered index on the partitioned column, partition elimination does provide a performance improvement. Obviously in your example, the clustered index is going to be the real performance enhancer but assuming the user cannot switch their clustered index to the column they need to partition on, partition elimination will give them some boost. And actually adding a filtered index on the partitioned column is even better, for example if they are partitioning on column audit_date and they know that 90% of the queries are related to records in the past 1 year. Setting up a filtered index to cover this often queried scenario will provide a massive performance boost. We set this scenario up on a partitioned audit history table which as of today has 257312895 rows and we see great query performance. But, I absolutely agree with your comment that the clustered index seek would by far beat out partition elimination when it comes to performance.
[font="Courier New"]Michael Connolly
[/font]
August 15, 2012 at 10:54 pm
Thank you all for your help full reply .
Basically i have table will large data and table had clustred index also . still query performance is poor b'coz most of filters on date column . so i wanted to partition on date column .
So now i wanna know how does it impove the performance . Am not getting the clear picture of how it works internally . When exactly partition helps me ?
Regards,
ravi@sql
August 16, 2012 at 1:55 am
Partitioning generally does not improve performance. To get a performance improvement out of it, you have to do careful design and a lot of testing.
Can you post the table, indexes and some of the queries?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply