0

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

Steve V
  • 101
  • 1
  • You should be able to use a FORCESEEK hint, or hint undocumented trace flag 8726 using QUERYTRACEON (per the linked Q&A). Let me know if that doesn't answer your question and we can reopen it. – Josh Darnell Mar 26 '20 at 17:32
  • Related: https://dba.stackexchange.com/questions/260880/why-are-seemingly-suitable-indexes-not-used-on-a-left-join-with-or – Josh Darnell Mar 26 '20 at 17:33

0 Answers0