July 6, 2012 at 7:27 am
I was recently tasked with testing what benefits paritioning could have on a table with about 14 years (50,000,000) worth of data in it.
So, I created a new version of this table that is partitioned into fiscal years, and has 15 partitions (1 for each year, and 1 empty).
Then, I ensured that the indexes on this table were also partioned in the same manner as the data.
Now I'm going through and running queries to test performance against both the partitioned table and the non-partitioned version.
So far, all the queries return in exactly the same time from each table.
Is this to be expected? If that is the case, what are the advantages to table partitioning?
I have found none so far.
July 6, 2012 at 7:56 am
Table and index partitioning can dramatically improve performance when partition elimination can be performed.
This means that the query predicates can help the optimizer choose which partition(s) to read and which partitions can be skipped.
If your queries do not contain a predicate on the partitioning key, no partition elimination can be performed. In some cases, this leads to WORSE performance than the non-partitioned table.
Before even considering table partitioning, you should examine the query patterns and choose an effective partitioning key. If no such key exists, you only get manageability benefits, such as sliding window table cleansing.
Hope this helps
Gianluca
-- Gianluca Sartori
July 6, 2012 at 11:28 am
Ok, I figured out that I needed to rebuilt my indexes that exist in the partitioned space (parition by partition) and then performance got much better.
Also, cummulative data...specifically anything from now to 3 years all the way to 12 years is significantly faster from the partitioned table.
Very interesting.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply