April 9, 2010 at 1:34 pm
I noticed in one of my recent queries that if the select statement against a target table resulted in an index scan it did't matter how I adjusted my joins on related tables they were doomed to scans as well. Does anyone know why this would be so?
April 9, 2010 at 1:52 pm
We can't answer that without seeing the query, table defs and .sqlplan.
Please send those over and we'll be able to point you in the right direction.
One point to have in mind is that it's less costly to do a table scan that doing even 50% of seek + lookups. I say 50% but I don't know the actual typing point and I'm sure it's WAY lower than 50%.
So that may explain why the optimizer is choosing that option however there may be loads of other possible explainations.
April 9, 2010 at 2:21 pm
After closer inspection I think the underlying issue is that the 'select from' table is a heap with several non-clustered indexes on frequently queried columns. What baffles me is that the joined tables are also heaps and the joins will seek if a seek is performed on the primary table. To go any further would be a waste of everyone's time. I was just hoping to find out if there was an underlying technical explanation.
Thanks for the post.
April 9, 2010 at 4:08 pm
Ultimately, query optimisation is an art more than anything else...
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply