October 15, 2003 at 3:13 pm
I have a table, which contains address information,
have non-clustered index on phone no, search on phone number was very quick, now users requested that search on address be possible too, I created non-clustered index on address which speeds up search by address but search by phone number is extremely slow, query plan shows that optimizer is using this new index instead of index on phone number even during phone search, I updated stats with full scan and still same result.
Dbcc show_statistics shows better selectivity for index on address than phone number.
It is a very complicated dynamic query which I cannot force optimizer to use specific index.
Any help will be appreciated
October 16, 2003 at 1:41 am
What makes the query so complicated that you can not use index hints? If it is complicated, how does a simple query behave ? Is it slow or fast if you select on phone-numbers only? In addition to your non-clustered indexes, do you also have a clustered index ?
One thing to try out: Do you experience a difference between dynamic and regular SQL queries? Maybe the optimizer treats them differently.
One more thing: is your phonenumber varchar or char(xx) ? Is the type in your query exactly the same as in your table definition? Same length ? The Optimizer may avoid using the index if types differ. One more thing which would stop the optimizier from using the phone-index: do you use "like" comparisons ?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply