Why is UNION faster than an OR statement

Question

I have a problem where I need to find records that either have a measurement that matches a value, or do not have that measurement at all. I solved that problem with three or four different approaches, using JOINs, using NOT IN and using NOT EXISTS. However, the query ended up being extremely slow every time. I then tried splitting the query in two, and they both run very fast (three seconds). But combining the queries using OR takes more than five minutes.

Reading on SO I tried UNION, which is very fast, but very inconvenient for the script I am using.

So two questions:

1. Why is UNION so much faster? (Or why is OR so slow)?
2. Is there any way I can force SQL Server to use a different approach for the OR statement that is fast?

Answer 1

The reason is that using OR in a query will often cause the Query Optimizer to abandon use of index seeks and revert to scans. If you look at the execution plans for your two queries, you'll most likely see scans where you are using the OR and seeks where you are using the UNION. Without seeing your query it's not really possible to give you any ideas on how you might be able to restructure the OR condition. But you may find that inserting the rows into a temporary table and joining on to it may yield a positive result.

Also, it is generally best to use UNION ALL rather than UNION if you want all results, as you remove the cost of row-matching.