CODEWITHSUNDEEP
sqlt-sqldatabase-design
Raffaele
Raffaele

Reputation: 20885

Evaluation order in WHERE clause and really slow query

SELECT ... WHERE expr1 and expr2 or expr3 or expr4

this query executes in > 3 minutes.

SELECT ... WHERE expr1 and (expr2 or expr3 or expr4)

this terminates instantly.

I would have expected different results, not different performance. Can anybody explain why?

Background: the select left outer joins 7 tables, but they are not very large (< 10k records the biggest one). Here is the query, but I strongly advise that it's unintelligible because it's generated from a very old tool on a legacy system and the overall project is very difficult to work with. I am only interested in the question descrived above

Upvotes: 1

Views: 349

Answers (2)

JNK
JNK

Reputation: 65157

OR comparisons take a long time, since you need to check every record for every possibility.

Encasing the ORs in parentheses treats them as a set and allows short-circuiting evaluations.

Your first query evaluates as:

WHERE (Expr1 --required
AND   Expr2) --required
OR    Expr3 --but you can skip those to find this
OR    Expr4 --or this

It takes more time to check all 3 conditions.

Your second evaluates Expr1, then can quickly check three conditions in the subset. I'm guessing that one of those is indexed and can be checked very efficiently, which reduces the result set needed to check for the others.

Upvotes: 2

ruakh
ruakh

Reputation: 183301

The second version requires that expr1 be true for all records. If expr1 depends only on a single table, then the DBMS can "push" the predicate "down" and do that filtering before even performing the joins. (Even if it depends on multiple tables, it can at least be pushed down before some of the joins.) This potentially allows many fewer records to be examined, and much better use of indices.

Upvotes: 2

Related Questions