Effiiency of multiple OR predicates versus a single range

Question

Say I have a SQL statement:

SELECT * 
 FROM MyTable 
WHERE KeyColumn1 = 1 OR 
      KeyColumn1 = 2 OR 
      KeyColumn1 =3 OR
      ... 
      KeyColumn1 = 10

And another:

SELECT * 
 FROM MyTable 
WHERE KeyColumn >= 1 AND 
      KeyColumn <= 10

Are they actually different and which one would be more efficient?

Answer 1

For 10 values where you have no index, this is such a micro-optimization that you shouldn't worry about it. The database has to read all the rows, and those reads are going to be less efficient that doing a few comparison operations.

The first can more efficiently be written as:

where KeyColumn in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

This should have the same efficiency as the or version for most database engines.

The better question is which way of describing these values better matches the business problem? You want the code to be easily understood and maintainable. Use a list if that makes sense. Use a range if that makes sense.

If you had hundreds of values, then definitely go with the second approach. It is cleaner and easier to understand and write and should be faster.

How you write the query does affect optimization, and that in turn depends on the database engine. Some databases are smarter about optimization than others. Most of the effect would be on the use of indexes and the effect that other filters in the where clause have on index usage.

Answer 2

For SQL Server, using a similar example with a clustered index on the Id, the plans both use a "Clustered Index Seek" which is a broad explanation for what may happen internally.

select ObjectId from oav.Object 
 where ObjectId in (3105680,3105681,3105682,3105683,3105684,3105685,3105686,3105687,3105688,3105689)

select ObjectId from oav.Object 
 where ObjectId between 3105680 and 3105689

However the query that specifies a range:

• Lists 2 seek predicates (>=, <=) instead of 10 (=)
• Has fewer reads (3 vs 30)

The test case alone is not complex enough to register other differences (CPU/Duration). However from the difference in read count, potentially there could be a significant runtime difference if this effect were magnified as part of a larger query or covered a larger range.

Using ">=" and "<=" was equivalent to "between". I didn't explicitly test "or" vs "in" (assume same).

For contiguous ranges, "between" is probably a better choice than "in".

---

Related reading:

• When is a Seek not a Seek
• Is it a Seek or a Scan?

Answer 3

It depends on the targeted SQL engine. Mainly because each engine has different approaches when it comes to scanning full tables, and using indexes.

If we talk about a non-indexed table or a hint that forces a full-table scan, there is not really a difference between the two examples, because at a lower level they're both filters, and will be treated in the same way.

On the other hand, when indexes are involved:

In SQL Server, you have basically two types of index operations: Scan and Seek. Their performance depends mainly on the percent of rows returned from the candidates (or total). As long as in the examples you provided you're using the same values/bounds, the operation is the same. It is either a Scan, or a Seek, filtered by your values.

In Oracle, there are more types of index scans. They're chosen by the optimizer depending on the how the indexes are built on the table. For instance when using bounded intervals (<= and =>) the optimizer will most likely chose a range scan; in the other example, it will perform either a skip scan, full scan, or even an unique scan. Their performance is subjected to the whole structure of the table, and again, on the percent of numbers returned.

In conclusion, neither of versions is universally better - it depends on your scenario. Even more, if you use the proper hints, you can end up having the same execution plans for both of them.

Answer 4

The second one is more efficient since there are fewer logical operations, in the first one the DB Manager has to evaluate the operation for each one of the "or" statements.