How data will get search in partitioned table which is having index ?

  • Dear All,

    How data will get search in partitioned table which is having index ?

    Regards,

    KoteRavindra

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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]

  • 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

  • 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]

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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]

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply