Mark Cohen is GM, Product Technology at Fairfax Media which is the most fun a job can be while still being legal. On weekends he may be found meandering along Bay parade with a coffee in Malabar. This is an old personal site (you can find Mark Cohen at his new vanity url. All views expressed are solely are my own. Except maybe the ones about short-circuit evaluation in SQL.

Short-circuit evaluation in SQL

I did some limited hallway testing today and discovered that a fair number of people do not know that SQL Server does short-circuit evaluation. For those who don’t know, the simple explanation is that SQL Server will evaluate the left hand side of a conditional statement with an AND in the middle and if it is false the SQL will not bother with the right.

To demonstrate, try this:

     select ‘short circuit’ where 1=0 and 1/0 = 0

If SQL supports short circuit evaluation nothing would be returned – obviously 1 is never equal to 0 :)

If SQL evaluated both sides, this would cause an error.

The implications of this on search procedures is simple yet massive. If a where clause has to check a value or ignore the check if the parameter is null, most people write this as

SELECT
     Fields
FROM
     Table
WHERE
     table.field = @parameter OR @parameter IS NULL

If there are enough parameters, this will quickly lead to performance problems. SQL will do the left comparison regardless of the value of the parameter for every parameter, for every row in the table. Many people will resort to dynamic SQL queries to work around this. Here’s another take: Use short-circuit evaluation:

SELECT
     Fields
FROM
     Table
WHERE
     NOT (@parameter IS NOT NULL AND table.field <> @parameter )

The left hand side will fail before the right hand side is even evaluated, and because the left hand side can be determined before the query runs, SQL is smart enough to evaluateit only once. The performance difference is worth investigating.

13 comments:

  1. Rommel Manalo, 12. July 2006, 13:51
     

    Why I am not able to do short ciruit evaluation here……
    SQL Statement:
    SELECT orgname FROM OrgView WHERE QID = 94 AND CONVERT(DATETIME,Answer) = ’2006-07-06′

    The OrgView contains ORGNAME and ANSWER field both string data type and QID with INT…
    Contains data….
    ANSWER field contains number 1 (one) value which is not convertible to date but the QID is NOT EQUAL 94

    I am expecting to do execute the first condition (QID=94) then the CONVERT(…)
    So to filter all records with 94 as QID then convert the Answer fields which contains convertible date string…
    BUT i got an error
    “Conversion failed when converting datetime from character string.”
    This is because the answer field contains value that is not convertible to datetime even the QID is NOT EQUAL 24

    BELOW is sample data
    ======================================================================
    ORGNAME QID ANSWER
    ======================================================================
    org1 120 1
    org2 94 2006-06-06
    org3 98 all about loving you
    ======================================================================

    there you go if i execute the sql above i got an error
    but hey look I got the first condition to search for QID is 94 and Answer with convertible data….

    thanks for the reply in advance………………

     
  2. Mark Cohen, 12. July 2006, 20:42
     

    I tried the following to get a quick rule-of-thumb:

    if (1=1) or (1/0 = 0)
    print ‘true’
    else
    print ‘false’

    Returned “true”

    if (1=0) or (1/0 = 0)
    print ‘true’
    else
    print ‘false’

    Threw an error: “Divide by zero error encountered.”

    if (1=0) and (1/0 = 0)
    print ‘true’
    else
    print ‘false’

    Returned false

    And lastly,
    if (1=1) and (1/0 = 0)
    print ‘true’
    else
    print ‘false’

    threw an error “Divide by zero error encountered.”

    This is a quick test that shows the short-circuit evaluation works. So without looking at your query I would deduce that the left hand side of the and is returning a true.

    Bizarrely enough, I tested your query and it worked.

    Here’s my test script:

    create table OrgView (ORGNAME varchar(100), QID int, ANSWER varchar(100))

    insert into orgview (orgname, qid, answer) values(‘org1′, 120, ’1′)
    insert into orgview (orgname, qid, answer) values(‘org2′, 94, ’2006-06-06′)
    insert into orgview (orgname, qid, answer) values(‘org3′, 98, ‘all about loving you’)

    SELECT orgname FROM OrgView
    WHERE QID = 94 AND CONVERT(DATETIME,Answer) = ’2006-06-06′

     
  3. Greg Martin, 13. June 2007, 18:33
     

    Excellent article, thanks. A question:

    “The implications of this on search procedures is simple yet massive. If a where clause has to check a value or ignore the check if the parameter is null, most people write this as

    SELECT
    Fields
    FROM
    Table
    WHERE
    table.field = @parameter OR @parameter IS NULL
    If there are enough parameters, this will quickly lead to performance problems. SQL will do the left comparison regardless of the value of the parameter for every parameter, for every row in the table. Many people will resort to dynamic SQL queries to work around this. Here’s another take: Use short-circuit evaluation:

    SELECT
    Fields
    FROM
    Table
    WHERE
    NOT (@parameter IS NOT NULL AND table.field @parameter )

    Unless I’m missing something, you would get the same good short-circuit evaluation effect by reversing the parts of the OR in the first example? i.e.

    WHERE
    @parameter IS NULL OR table.field = @parameter

     
  4. Mark Cohen, 13. June 2007, 20:13
     

    it’s been two years since I wrote this, I had to come back and try figure out what I was talking about ;) I’m not sure what I was doing when I lifted the example, but what you say above is correct – the important thing is the @parameter is null is on the left, not the wierd double negative :)

    Mark

     
  5. Greg Martin, 13. June 2007, 21:35
     

    Still coming up first for ‘short circuit evaluation sql’ on google!
    :)

     
  6. sc0rp10n, 25. January 2008, 8:54
     

    Unfortunately these statements are not entirely accurate. In slightly more complicated scenarios you are unable to short circuit the evaluation of a clause using boolean logic. I’m only pointing this out here becuase this page is the top result on a google search for short circuit evaluation.

    You have shown that when using literals, short circuiting does happen. However, when dealing with sets of data you will find that your tests fail. For example, run the following:

    CREATE TABLE ShortCircuit (
    ID int )
    GO

    INSERT INTO ShortCircuit
    SELECT 1 UNION
    SELECT 2 UNION
    SELECT 3 UNION
    SELECT 4 UNION
    SELECT 5 UNION
    SELECT 6
    GO

    DECLARE @ID int
    SET @ID = NULL

    SELECT ID
    FROM ShortCircuit
    WHERE @ID IS NULL OR ID = @ID

    DROP TABLE ShortCircuit

    Following your logic, the clause would see that @ID is indeed null and short circuit. However, the following shows this to be untrue:

    CREATE TABLE ShortCircuit (
    ID int )
    GO

    INSERT INTO ShortCircuit
    SELECT 1 UNION
    SELECT 2 UNION
    SELECT 3 UNION
    SELECT 4 UNION
    SELECT 5 UNION
    SELECT 6
    GO

    DECLARE @ID int
    SET @ID = NULL

    SELECT ID
    FROM ShortCircuit
    WHERE @ID IS NULL OR ID = 1/0

    DROP TABLE ShortCircuit

    The divide by zero error is thrown showing that the statement did not short circuit. The reason why can be shown in this example:

    CREATE TABLE ShortCircuit (
    ID int )
    GO

    INSERT INTO ShortCircuit
    SELECT 1 UNION
    SELECT 2 UNION
    SELECT 3 UNION
    SELECT 4 UNION
    SELECT 5 UNION
    SELECT 6
    GO

    DECLARE @ID int
    SET @ID = 1

    SELECT ID
    FROM ShortCircuit
    WHERE ID = @ID OR ID IN (2, 3)

    DROP TABLE ShortCircuit

    If the evaluation were to short after the initial true, then only 1 row would be returned, neglecting the other 2 rows we also desire. So while short circuiting may work with the literals shown above it does not in fact work in practice for querying. This can have a large impact on query times if an unknowing developer is assuming that boolean logic will short circuit; especially when using nested queries such as the following:

    SELECT *
    FROM TableA
    WHERE (@ID IS NULL OR ID = @ID)
    AND (@FK1 IS NULL OR FK1 IN (
    SELECT ID
    FROM TableB )
    AND (@FK2 IS NULL OR FK2 IN (
    SELECT ID
    FROM TableC )

    This may seem like a great way to ignore null parameters, but it can dramatically impact the performance of a query; going from milliseconds to several seconds or more.

    For true short circuit evaluation, the only method I am aware of is the use of the CASE function.

     
  7. Shiju Samuel, 27. February 2008, 3:09
     

    Below doesn’t work. shouldn’t the short circuit evaluation work?

    SELECT orgname FROM OrgView
    WHERE isdate(Answer) = 1 and CONVERT(DATETIME,Answer) = ’2006-06-06′

    I am getting conversion error here.

     
  8. Eric Moreau, 7. April 2008, 22:09
     

    Is it possible that the IsNull statement is not doing short-circuiting?

    For example, consider I have these statements:
    DECLARE @test INT

    SET @test = 1
    SET @test = ISNULL(@test, (SELECT dbo.UF_errorDoneWithErrors(NULL,NULL)))
    SELECT @test

    SET @test = NULL
    SET @test = ISNULL(@test, (SELECT dbo.UF_errorDoneWithErrors(NULL,NULL)))
    SELECT @test

    When I debug, In both cases the debugger steps into the function.

     
  9. Mark Cohen, 29. October 2009, 17:46
     

    @sc0rp10n – I’d suggest that nested queries would be a less effective way of doing what you’re sketching out and that sql would not short-circuit because it evaluates from the innermost queries outwards…

     
  10. Jeff French, 10. December 2009, 10:10
     

    I just read a blog post that gives a good example of when and why short circuit evaluation will fail.

    http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/

    It shows that based on the type of data, number of rows in the table and configuration of non-clustered indexes can cause the query optimizer to evaluate the right side of an expression first in some cases. You must use CASE statements (even though they are not ideal) to garuntee correct (and ordered) evaluation of conditions.

     
  11. Richard C, 13. July 2010, 18:00
     

    Jeff’s already posted the counter. But I was working up my own as well using your sample test.

    create table OrgView (ORGNAME varchar(100), QID int, ANSWER varchar(100))

    insert into orgview (orgname, qid, answer) values(‘org1′, 120, ’1′)
    insert into orgview (orgname, qid, answer) values(‘org2′, 94, ’2006-06-06′)
    insert into orgview (orgname, qid, answer) values(‘org3′, 98, ‘all about loving you’)

    SELECT orgname FROM OrgView
    WHERE ANSWER IS NOT NULL AND qid/0 = 1

    The point is that yes, SQL Server will perform short circuit evaluation.
    But NO, it is not left to right.
    It is determined by the query optimizer based on least cost.

    In my revised form above, IS NOT NULL on a text field is more expensive than integer division, so SQL Server zooms in on that one first. Whoalla, bang – divide by zero.

    QED

     
  12. Brandon K, 9. March 2011, 7:41
     

    I don’t think SQL Server 2008R2 uses logical short circuiting with CASE statements.

    Example:

    SELECT
    CASE WHEN 1 = 0 THEN 1
    ELSE SUBSTRING(‘ABC’,0,-1)
    END

    Error: Invalid length parameter passed to the substring function.

    Why the heck is SQL Server trying to execute substring in this case is beyond me…

     
  13. Brandon K, 9. March 2011, 7:42
     

    Woops! I meant to write:

    SELECT
    CASE WHEN 1 = 1 THEN 1
    ELSE SUBSTRING(‘ABC’,0,-1)
    END

    The result is the same :(

     

Write a comment: