Does the order of where clauses matter?

  • Suppose I have a table called PEOPLE having three sections, ID, LastName, and FirstName. These segments are not really recorded. LastName is more interesting, and FirstName is less remarkable.

    Assuming I do two hunts:

    select * from PEOPLE where FirstName="F" and LastName="L"
    select * from PEOPLE where LastName="L" and FirstName="F"

    My belief is the second one is faster because the more unique criterion (LastName) comes first in the where clause, and records will get eliminated more efficiently. See this Here, I also don't think the optimizer is smart enough to optimize the first SQL query.

    Any Suggestions, Thanks in advance.

  • It generally matters little, if at all -- the optimizer should be able to use statistics to order the predicates optimally.

    Far more important are statistics and indexing. Without an appropriate index, the query will have to scan the table or some index. If the first n columns in an index match search predicates, the query engine can seek or at least scan a subset. Without up-to-date statistics, the optimizer may make bad guesses on which index to use and how to use it.

    An index on FirstName and LastName, with one of them being the first column in the index and the other being the second column in the index, would probably be able to directly seek the matching rows with either of these queries.

    An index starting w/ FirstName (but not LastName) would be able to find all the "F"s, but would then still have to scan through the LastNames to find "L".

    An index starting w/ LastName (but not FirstName) would be able to find all the "L"s, but would then still have to scan through the FirstNames to find "F".

  • The optimizer will absolutely rearrange your WHERE clause. It's part of how it works. It'll also rearrange and reorder your JOINs. So, from the most simplistic approach, WHERE & JOIN order don't matter.

    HOWEVER...

    The optimizer only uses so many resources and so much time it can spend doing this sort of thing. So, it's a good habit to get into ordering your JOIN and WHERE criteria so that you put the most restrictive stuff first. Sometimes, you doing it will actually work better than relying on the optimizer to do it.

    THAT SAID...

    In the example you give, the optimizer will absolutely be able to figure it all out. After that, everything @ratbak said.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • To add to what ratbak and Grant have stated, the other thing that paying attention to the order of the criteria in the WHERE helps with troubleshooting.  Yeah... I know that sounds strange but it helps others more easily understand the code as a whole.

    Combining that a bit with what Grant said, I normally put the order of the tables in the FROM clause as those that return the smallest result sets first and then the criteria in the WHERE in the same order.  There are always exceptions to every such "general" rule but that's what I normally do.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply