SQL Server Performance Forum – Threads Archive

prioritize my where condition

Hello! I have query like this select
fromUserID
from
messages
where
erased=0
and
fromUserID=12 The query above will take ~ 50 seconds to execute. If I change the query to select
fromUserID
from
messages
where
fromUserID=12 It will take < 1 second to execute. It seems like it run "where erased=0" before "where fromUserID=12". How can I define which statements in my where condition to run before another? (I have tried to switch places with no luck)
I know I can state it as a sub query, but is that the best? Any suggestions?

SQL Server’s query optimizer doesn’t care what order the WHERE clause is, as it will take what you have and try every potential way to speed up the query on its own. You need to take a look at the execution plan of each query and see what is happening. My guess is that the slow onw is not using an index, and the second one is using an index. If this is the case, consider adding an index to the "erased" column and see what happens. If there is an index on this column already, and it is not being used, it may be because the index is not selective enough. If this is the case, you may want to experiment with using a hint to force the first (slow) query touse the index from the "fromUserID" column and see what happens. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

There is probably an index on fromUserID, and when erased=0 is omitted then SQL won’t need to access the table. One option is to change the index so that it contains both fromUserID and erased Cheers
Twan

Erased=0 hints at a column that has a bit type (or some flagging logic of the yes/no kind) and may have issues with index usefulness due to the density/selectivity criteria that the query optimizer uses. The statistical spread of the data in that column, even with an index, may force the optimizer to use a different execution plan than would otherwise be expected. Think about it. Nathan H.O.
Moderator
SQL-Server-Performance.com

Brimba I agree with Twain, Probably adding an composite index for (fromUserID, erased) will help in a faster query. Reason for this order is selectivity creteria as i think erased will not take many values except 0,1.
And while querying you should keep the sequence in where clause as fromUserID, erased only and not the other way round. HTH
Harsh

Hi Harsh, Why do you recommend the order in the where clauses? The order won’t make any difference…? As far as I’m aware the only order that does matter is the order of tables in the from clause when there are a large number of them, as I’m fairly certain that SQLServer still behaves like its old Sybase cousin which takes groups of tables and optimises them in turn rather than trying to optimise every permutation of table order combination. This is one of the main reasons why the new ANSI join is preferred Cheers
Twan

Twan
Sorry, I confused the WHERE clause order with INDEX order…i refreshed my memory by reading Inside SQL Server. The column order matters in INDEX…as far as WHERE clause is concerned, it SHOULD have LEFT MOST columns of COMPOSITE INDEX. i.e. if index is on (fromUserID, erased), either WHERE clause should have both columns(In any order…OPTIMISER will take care of that) or just fromUserID. Index will not be used by SQL if only erased is selected. I Apologise for previous mistake regarding WHERE clause. Harsh

hey no apologies necessary, I was only trying to find out if you knew something that I could learn <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ />

I ran UPDATE_STATISTICS mytable and now it runs quickly. This was on my dev server. fromUserID (int) erased (bit) There could be a possibility of creating an index with erased and fromUserID but since I also have toUserID and other stuff in the table I dont think that would matter much. Since erased can be either true/false. I figured out that it was my statistics that wasent updated on that table. There should also be possible to join, but that would also be slow I think. I think the SQL-servers statistics on that table were some kind of corrupt and it scanned the erased column before fromUserID of some reason. Anyone have an idea of why? I think a bit column should always be a slow way of choosing data? But when the statistics were updated SQL-Server noticed it had to scan fromUserID first and then check the erased column. hmmm.. seems like I need to learn some more about this <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Thanks for the help everyone!

If you have created the index on the combination of fromUserID and erased, then that will be what made the difference… The query is ‘covered’ using that index, it won’t need to go to the table at all eventhough there is other stuff in the table. You could check this by dropping the index and seeing if performance is still fast Cheers
Twan

]]>