Azure SQL Execution of second AND condition when the first one is false

Question

I have the following problem:

When i execute a query on Azure SQL, the second AND condition runs, even when the first one is false.
The table "items" contains 2048 items with the column 'Claimd' =1 and only one item with Claimd=0. That one item is also the item which has a description containing the word "razer".

SET STATISTICS TIME on
SELECT * FROM dbo.Items
WHERE Claimd=0 AND
([Description] LIKE '%razer%' OR [Name] LIKE '%razer%')

Result: elapsed time of 143 ms

If I just search on Description, I get the following result:

SET STATISTICS TIME on
SELECT * FROM dbo.Items
WHERE Claimd=0 AND
[Description] LIKE '%razer%'

Result: elapsed time of 1 ms

There is only one item with Claimd=0, so that explains why the result is show in 1 ms. But when I want to search on a second column, with an OR condition, it's like it's searching the whole table again instead of only those with the flag "Claimd"=0

Is there something wrong with my brackets? I would really like to know why that second AND statement is executing when adding an OR statement, even if the first statement is false.

And your times are reproducible? (Do you see the same time difference if running the queries in reverse order? There may be caching issues.) I don't know much about Azure—is there a way of showing the query plan? — Matt Gibson, Nov 03 '14 at 23:19
(Also: If you want it to use Claimd by preference, try putting an index on it.) — Matt Gibson, Nov 03 '14 at 23:24
Yes, it doesn't matter what order I run them, the query with the search on two columns will always be around 150 ms (probably checking all the items (even with Claimd=1)), while the query that searches on only one column will always be 1-10ms). There is a way to view these plans. The slow one takes 64% CPU time on filtering, while the fast one doesn't mention "filtering" — Jaron Fontaine, Nov 03 '14 at 23:31
I think you solved the problem, thank you! Putting an index on Claimd worked! — Jaron Fontaine, Nov 03 '14 at 23:37

score 0 · Answer 1 · edited May 23 '17 at 12:13

According to this article, as well as other answers on Stack Overflow here and here, the ANSI SQL standard makes no explicit guarantee of a short circuit.

Another possible reason for the discrepancy in your times could be an index on [Description], and none on Claimd. This would also explain why the 2nd query was so much quicker - it was using an index that the first one couldn't). [Not saying this is your issue, just that it could be without knowing more about the indexes and execution plans]

Matt Gibson · Accepted Answer · 2014-11-04T10:12:32.900

In effect, there is no "first condition" and "second condition" here—the query optimiser will decide which to evaluate first based on its current guess as to what will give the faster answer.

It will decide that based on the query, the table indexes, the data types, and the data itself (a query optimiser typically uses statistics gathered from the actual data in each table to give itself more of a clue as to what's best, e.g. it knows that a table scan of a table with not much data is generally okay.)

As you don't have an index on any of the relevant columns in your query, my suggestion would be simply to add an index on Claimd. This should be a nice big hint to the optimiser that cutting the data based on Claimd will be the fastest solution. It should also improve the speed of even your "best case" search, though with a 1ms result already you might have a problem noticing that.

Adding indexes based on your knowledge of the queries you'll be running and the likely distribution of values in a column is generally a good thing, though be careful not to optimise too early. If that 143ms wasn't actually causing a problem, it's possible it might never have caused a problem: as more data was added to the table the query optimiser might have changed its strategy to at least table scan on Claimd first in all cases. Query optimisers are hard beasts to outguess.

Azure SQL Execution of second AND condition when the first one is false

2 Answers2