Post reply

OR / IN

  • January 19, 2004 at 4:40 pm

    #107296

    Dumb question.....But Which one is the best i can choose between OR or IN operators in a query

    Any idea?

    Shas3

  • January 19, 2004 at 9:10 pm

    #490766

    I prefer "IN" for readability and maintenance.  Haven't checked performance though.

  • January 20, 2004 at 7:24 am

    #490810

    If you look at query plan for a query with an "IN" clause with 2 or more values, you'll see an "OR" happening, so perfomance wise, IN & OR may be on par with each other. OR can be very perfomance detrimental as we all know. 

    An alterative, if you want a result set having multiple matching criteria, is to use UNION ALL instead, this can eliminate the ORing and often provide a much faster query result.

    Another alternative if to have the multiple matching critera values either in a #Temp table or a Derived table and then JOIN the original query with it. This can also eliminate the ORing and provide better perfomance.

    In performance critical queries, test multiple methods, hopefully against "real" data. One easy way to test whether one query plan is better than another, is in Q/A, write both queries, then "Display Estimated Execution Plan" for both (or 3, 4... diff) at the same time. Each query will show a percentage of the entire execution, thus if you test 2 queries, conbined, both will use up 100% of the execution time, but one may only use up 10% while the other uses 90%, making the 10% usage query "estimated" to be much faster.

     

     



    Once you understand the BITs, all the pieces come together

Viewing 3 posts - 1 through 2 (of 2 total)

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