SQL Server Performance

Performance - WHERE condition order

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by vspin, Feb 26, 2008.

  1. vspin New Member

    I asked a question on experts-exchange.com and got two different answers. Who is right?
    I asked if I run this query:

    SQL = "SELECT * FROM tblname WHERE (colname1 = 1) AND (TimeConsumingFunction(colname2) = 5786)"
    Would it be faster than this query?:
    SQL = "SELECT * FROM tblname WHERE (TimeConsumingFunction(colname2) = 5786) AND (colname1 = 1)"
    (note: I only changed the condition order.)

  2. satya Moderator

    What is the index criteria on this table?
  3. ranjitjain New Member

    Have you looked at the execution plan generated for both queries.
  4. vspin New Member

    <<What is the index criteria on this table?>>

    Primary key field only (ID)

    <<Have you looked at the execution plan generated for both queries.>>

    No, I was just thinking this up. I figure, why would SQL Server not check if the first condition is true and if not, go to the next record rather than checking ALL conditions..?
  5. vspin New Member

    In case anyone comes across this thread, angelIII (of experts-exchange.com) suggested the following subquery:

    SQL = "
    SELECT *
    FROM tblname
    WHERE colname1 = 1
    ) subquery
    WHERE TimeConsumingFunction(colname2) = 5786
    If he's right that order means nothing, then this seems to be the best solution to a slow WHERE condition.
  6. ScottPletcher New Member

    Since SQL generally works left-to-right, and can short-circuit where appropriate, this version is better:
    WHERE (colname1 = 1) AND (TimeConsumingFunction(colname2) = 5786)
    However, note that time is saved *only* if the first condition is *false*. If the first is true, SQL must check the second anyway.
    That's why I think angeliii's subquery method should perform worse that the version above as well ... although SQL has been known to produce odd results on occassion [:)].

    Thus, overall, I do think angeliii is wrong about this. And it's easy enough to prove that SQL does short-circuit (i.e. skip unneeded tests) when possible. Run this code:
    DECLARE @var1 INT
    SET @var1 = 2
    WHILE @var1 >= 1
    IF @var1 = 1 AND DATEADD(DAY, 1, '19001131') < '20000101'
    PRINT 'True'
    PRINT 'False'
    SET @var1 = @var1 - 1
    Note that the first pass, with a value of 2, completes, even though an invalid date was provided to DATEADD -- proof that SQL did not even attempt to run the DATEADD(), since the first statement was false.
    In the second pass, the first condition is true, so to resolve the "AND" SQL must evaluate the second expression.
    Bottom line: when possible, put the fastest and/or most determining condition(s) first.
    It is true that SQL can re-arrange the order of conditions. But it doesn't do it willy-nilly, so you still should check simple conditions in the best order yourself.
  7. vspin New Member

    ScottPletcher, I can't thank you enough for such informative information! Just what I was looking for. :)
    I wonder why this performance tip is not noted in more places..
  8. Adriaan New Member

    Better still - try to find a solution that eliminates the UDF from the WHERE statement. The use of the UDF means indexes are getting ignored.
    To force the order of the conditions, the solution with a derived table that vspin posted should work at all times.
  9. Madhivanan Moderator

  10. spirit1 New Member

    subquery doesn't mean forced order evaluation.
    USE AdventureWorks
    SELECT *
    SELECT *
    FROM Person.Address
    WHERE AddressID / 0 = 1
    ) AS t1
    WHERE StateProvinceID = 78
    SELECT *
    SELECT *
    FROM Person.Address
    WHERE StateProvinceID = 78
    ) AS t1
    WHERE AddressID / 0 = 1
    if it would then the first query would error out and the second wouldn't. but neither error out
    Order means nothing.
    You CAN'T force order in where codition evaluation!
  11. Madhivanan Moderator

    Mladen, nice to see you here after long time [:)]

Share This Page