Let's assume we have following query (greatly simplified, of course):
SELECT * FROM MyTable
Where A or B
The query takes about 30 seconds to run
When I rewrite the query as
SELECT DISTINCT * FROM
(
SELECT * FROM MyTable Where A
UNION ALL
SELECT * FROM MyTable Where B
) q
then I get results in about 4 seconds. I have seen the same pattern multiple times in the past - replacing OR with UNION improves performance a lot
Question: is there a way to make SQL Server to convert all those OR conditions to UNION internally, while preparing execution plan?
I was trying to find a query hint but no luck so far. We are using SQL Server 2016 SP2