April 10, 2008 at 5:05 pm
Several inner join queries are not using the right indexes. The join columns are of the same datype ‘int’, ‘not null’ status. The queries do make use of an index but the wrong index. The join columns are not a part of the ‘select’ statement.
Made sure the table/index statistics have been update. No fragmentation. I exported the data to a file. Dropped and re-created the table, indexes. Then re-compiled the stored procedure/functions. No difference
For some of the queries forcing the right index works but for some it still doesn’t use the forced index.
Any suggestions will be real helpful.
Thanks in advance.
April 10, 2008 at 8:56 pm
"For some of the queries forcing the right index works but for some it still doesn’t use the forced index."
Did you mean using index hints on some queries yet it still did not use that index?
Have you validated which one is faster? The one that query optimizer used, or when you override it? I've read that query optimizer may occassionally make mistakes :w00t:. In this case, index hint is your friend.
Have you looked into the order of your join tables? Perhaps SET FORCEPLAN helps.
_____________
Donn Policarpio
April 10, 2008 at 9:45 pm
Yes, after using 'with index', the optimizer still did not use the forced index.
The tables contain around 30,000 rows.
No, did not use 'set forcedplan'. Will try that.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply