Why is SQL evaluating a WHERE clause that is False?

Question

I've got a query in SQL (2008) that I can't understand why it's taking so much longer to evaluate if I include a clause in a WHERE statement that shouldn't affect the result. Here is an example of the query:

declare @includeAll bit = 0;

    SELECT
        Id
        ,Name
        ,Total
    FROM
        MyTable
    WHERE
        @includeAll = 1 OR Id = 3926

Obviously, in this case, the @includeAll = 1 will evaluate false; however, including that increases the time of the query as if it were always true. The result I get is correct with or without that clause: I only get the 1 entry with Id = 3926, but (in my real-world query) including that line increases the query time from < 0 seconds to about 7 minutes...so it seems it's running the query as if the statement were true, even though it's not, but still returning the correct results.

Any light that can be shed on why would be helpful. Also, if you have a suggestion on working around it I'd take it. I want to have a clause such as this one so that I can include a parameter in a stored procedure that will make it disregard the Id that it has and return all results if set to true, but I can't allow that to affect the performance when simply trying to get one record.

Answer 1

You'd need to look at the query plan to be sure, but using OR will often make it scan like this in some DBMS.

Also, read @Bogdan Sahlean's response for some great details as why this happening.

This may not work, but you can try something like if you need to stick with straight SQL:

SELECT
    Id
   ,Name
   ,Total
FROM
    MyTable
WHERE Id = 3926
UNION ALL
SELECT
    Id
   ,Name
   ,Total
FROM
    MyTable
WHERE Id <> 3926
AND   @includeAll = 1

If you are using a stored procedure, you could conditionally run the SQL either way instead which is probably more efficient.

Something like:

if @includeAll = 0 then
    SELECT
        Id
       ,Name
       ,Total
    FROM
        MyTable
    WHERE Id = 3926
else
    SELECT
        Id
       ,Name
       ,Total
    FROM
        MyTable