Post reply

Partitioning

  • October 8, 2011 at 6:16 am

    #260130

    Hi,

    I've an Items table partitioned on item_id, which is also a primary key and clustered index. The partitioning scheme puts 500K of item_ids per partition. For reretrieval of information from Items tables based on items_id, would it be beneficial to add partition id to Items table as part of clustered index? I was thinking about creating a function that returns partition id based on items_id, then use both partition id and items_id when joining to Items table. Would it improve performance to have partition id in the table? Or having to make an additional call to a function to determine partition id would just slow things down in an OLTP system?

    Thanks

  • October 8, 2011 at 7:42 pm

    #1392141

    You can return the partition ID this way:

    $partition.partitionFunctionName(partitionColumn)

    http://sqlvince.blogspot.com/[/url]

  • October 8, 2011 at 9:43 pm

    #1392155

    Joining to a table on a single int clustered primary key is as about as efficient as it gets. ( clustered index seek)

    Adding partition _id will bloat your non clustered indexes and also effectively preclude merge joins based on item_id.

    Do you have a particular query that isn't performing?

    Otherwise, i'd leave it alone.

  • October 9, 2011 at 7:13 am

    #1392182

    Lexa (10/8/2011)

    I've an Items table partitioned on item_id, which is also a primary key and clustered index. The partitioning scheme puts 500K of item_ids per partition. For reretrieval of information from Items tables based on items_id, would it be beneficial to add partition id to Items table as part of clustered index?

    Optimizer knows which specific partitions to touch therefore - since query is going to access data by PK - it doesn't make sense to manually point to a specific one.

    If in doubt, you may want to implement in development database and compare execution plans.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Viewing 4 posts - 1 through 3 (of 3 total)

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