February 16, 2009 at 7:58 am
I have a local partitioned view of a FactTable that is partitioned on surrogate key to a TimeDimension (smallint key). I found that use the TimeKey in a where clause perform well because SQL correctly eliminates the partitions that will not be part of the query based on the table constraints (eg. SELECT * FROM FactTable WHERE TimeKey > 6689). However, if I join the TimeDimension table and filter on a date, the query is slow and no partitions are eliminated (eg. SELECT * FROM FactTable F JOIN TimeDimension T ON F.TimeKey = T.TimeKey WHERE T.full_date > '2/16/09')
I can kind of understand why this is the case becasue SQL doesn't know that the natual keys in the dimension table are in any particular order so it doesn't know that the corresponding surrogate keys will be in any particular range of valuse, so it has to query all partitions of the FactTable. I think it would be much more effective for the optimizer to spend a little more time in the Dimension table before jumping to the FactTable. It would be easy to query the max/min surrogate-key values and use those to eliminate partitions.
I tried to work around this by creating a view that joins the TimeDimension with each partition table and specifying a WHERE CLAUSE with the date range for each. I do a UNION ALL to combine all of these joined-partitions into one view. Now the view works well with date criteria, but performance with non-date criteria plummets because the hard-coded date criteria dominates.
February 17, 2009 at 2:17 pm
you will need an index to cover the where clause - queries against partitions can be a bit strange sometimes - I found that queries often touched each partition but didn't actually do any io. Without having it all in front of me it's a bit tricky, sorry.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply