November 18, 2008 at 6:30 am
I did a database partitioning. Do i also need to make some change in existing SPs to speed up the data extraction process from table?
November 19, 2008 at 2:48 am
Including the partitioning key in queries will allow the optimizer to only consider relevant partitions when performing reads.
November 19, 2008 at 3:30 am
Nitin (11/18/2008)
I did a database partitioning.
Do you really mean "database partitioning" like in a distributed database environment or are your just referring to partitioning a couple of tables?
_____________________________________
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.November 19, 2008 at 2:39 pm
Christopher is correct. Add your partitioning key, always. Without the partitioning key, the query will basically end up scanning all partitions (unless you have a non-partitioned index).
If you're not sure of the correct range, try guessing at a large range... it should still perform better than not using the partitioning key at all.
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
August 30, 2010 at 10:21 am
christopher.cornish (11/19/2008)
Including the partitioning key in queries will allow the optimizer to only consider relevant partitions when performing reads.
2 questions -
1. "Including the partitioning key" - do you do this with a query hint?
2. Where is a good place to start doing my research on partitioning? I've read a little bit on it, but I'm kinda looking for a "partitioning 2005 sql server for dummies" kinda reference.
Thanks in advance for any responses to this post.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply