Disclaimer: For those of you looking for the "Where's the Order By Clause", then try googling again. You messed up.
Now on to the post....
Many people, once they start getting comfortable writing SQL, begin asking the same questions. One such common question is “Does it matter the order of stuff that I put on the where clause?” This question usually means different things to different people, so let’s dig in a bit.
The short answer, with Microsoft SQL Server, is “No.” SQL Server will internally re-order where clause items to properly match indexes or other time saving devices. The only exception to this is if items are grouped with parentheses.
That being said, other Database Management Systems (DBMS) may be impacted by where clause order. I used to work with Sybase SQL Anywhere and Pervasive DBs that it did make a difference. If you’re not working with SQL Server, take all this with a grain of salt and do you own testing.
I said earlier that this question means different things to different people. The following examples will highlight what I mean:
These two statements are equivalent in Microsoft SQL Server:
SELECT CUSTOMER_ANNOYACE_FACTOR
FROM CUSTOMERS
WHERE HAVE_CUSSED_ME_BEFORE = 1
AND STOLE_MY_GIRLFRIEND = 0
And
SELECT CUSTOMER_ANNOYACE_FACTOR
FROM CUSTOMERS
WHERE STOLE_MY_GIRLFRIEND = 0
AND HAVE_CUSSED_ME_BEFORE = 1
Notice that the first statement begins by filtering on “Have Cussed Me Before” and in the second statement, these are reversed.
As well, others may actually be asking about the following example. Again, these two are equivalent:
SELECT GEEK_LEVEL
FROM GEEK_RATINGS
WHERE GEEK_LEVEL = 25
And
SELECT GEEK_LEVEL
FROM GEEK_RATINGS
WHERE 25 = GEEK_LEVEL
Well, there ya have it. Yet another fine advanced SQL (OK, maybe not so advanced this time) from the Acme Advanced SQL Tuning squad.
Happy Tuning!
Subscribe to:
Post Comments (Atom)
2 comments:
In your experience with DBMS', have you ever used Firebird? I have recently ran into a situation where I found that it's optimizer failed to properly create a query plan so I positioned one inner join in front of another and it made it much faster. Thus, I wondered if it would have the same affect by changing around the where clause.
Apparently, Firebird users are extremely prideful of their optimizer and will not answer yes/no to this question in one forum. They keep telling me that the optimizer "should" take care of it. However, in situations where it DOESN'T take care of it I'm left wondering what to do besides adding/changing indexes, etc. Just wondering what you thought.
Thank you.
We haven’t had any experience with Firebird. As a general comment, however, no optimizer in the world can be perfect. After all, at the end of the day, these are just stupid computers doing exactly what the programmers ask them to. Also, at the end of the day, the programmers are far from perfect as well, and cannot anticipate all scenarios.
Post a Comment