February 27, 2008 at 7:04 am
All,
I have a partitioned table (1.7 billion rows) that is across 97 partions. The table is partitioned on datetime field.
When I query only this table with datetime field in where clause I see that cleary it outperforms the unpartitioned table.
When this table is joined to 3 tables it performs no better than
the non-partitioned table... it seems that the query does a partition scan.
The part. table has a 3 part composite clustered index and
a NC index on the partion field value.
Would appreciate any feedback.
thanks
February 27, 2008 at 7:12 am
Can you post the code of the query that performs well and the query that doesn't? That would help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 3, 2008 at 12:57 pm
partitioned tables can be a bit odd in joins - tricky for us to advise - I assume the join column(s) on the partitioned table are indexed? you may find a non clustere dinde xsover the top of the clustered index may help - also depends upon what is in the where and select clause of the query which foreces a scan presumably?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply