CODEWITHSUNDEEP
sqloptimizationconditional-statements
Steve Wortham
Steve Wortham

Reputation: 22240

Which conditional statement is faster in SQL?

SELECT a, b FROM products WHERE (a = 1 OR b = 2)

or...

SELECT a, b FROM products WHERE NOT (a != 1 AND b != 2)

Both statements should achieve the same results. However, the second one avoids the infamously slow "OR" operand in SQL. Does that make the 2nd statement faster?

Upvotes: 1

Views: 2478

Answers (5)

A-K
A-K

Reputation: 17090

SQL Server rewrites all queries before optimizing, and most likely both queries will be the same after rewriting. YOu can examine their execution plans in SSMS, just hit Ctrl+L, most likely they will be the same.

Also run the following:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

and rerun your queries - you should see identical real execution costs.

Upvotes: 1

ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells

Reputation: 66662

Traditionally the latter was easier for the optimiser to deal with in that it could easily resolve an and to a s-arg, which (loosely speaking) is a predicate that can be resolved using an index.

Historically, query optimisers could not resolve OR statements to s-args and queries using OR predicates could not make effective use of indexes. Thus, the recommendation was to avoid it and re-cast the query in terms like the latter example. More recent optimisers are better at recognising OR statements that are amenable to this transform, but complex OR statements may still confuse them, resulting in unnecessary table scans.

This is the origin of the 'OR is slow' meme. The performance is nothing to do with the efficiency of processing the expression but rather the ability of the optimiser to recognise opportunities to make use of indexes.

Upvotes: 4

Akash Kava
Akash Kava

Reputation: 39946

Ideally OR should be faster in this case because for every n steps, if it already found a=1 then it will not test second condition. Also there is no inverse operator (NOT) involved.

However for AND to be true, SQL has to test both the conditions, so for every n steps there are 2n conditions evaluated where else in OR, the number of conditions evaluated will always be less then 2n. Plus it has an additional operator to be evaluated.

However if one of the a or b is indexed, the query execution plan may differ because indexed column comparison involves intersect and union join operations over individual compare result sets !!

Also it would be wrong to consider OR as slow operator, when you consider your complex queries with joins over multiple tables, that time OR could be a big problem as mentioned by other contributor in this question. But for smaller query, OR should be fine. Infact every query has its own challenges, it not only depends on whats documented on help file, but also depends on how your data is distributed, its repeatation and variance factor.

Upvotes: 0

Steve Kass
Steve Kass

Reputation: 7184

There are no inherently slow or fast operators in SQL. When you issue a query, you describe the results you want. If two semantically identical queries (especially simple ones like this) yield very different run times, your SQL implementation is not very clever.

Upvotes: 1

automatic
automatic

Reputation: 2737

No, a != 1 and b != 2 is identical to a = 1 or b = 2.

The query optimizer will run the same query plan for both, at least in any marginally sophisticated implementation of Sql.

Upvotes: 1

Related Questions