Sql-server – Optimize performance for query with lots of OR’s

index-tuningquery-performancesql server

I have the following query

select Id
from Table1
where column1 = @inputparameter1
or column2 = @inputparameter1
or column3 = @inputparameter1
or column4 = @inputparameter1
or column5 = @inputparameter1
or column6 = @inputparameter1
or column7 = @inputparameter1
or column8 = @inputparameter1
or column9 = @inputparameter1

this query runs quite often from the app tier and so far the only way I have been able to make it run fast is by creating a filtered index on each of the columns in the WHERE clause (it does 9 index seeks + merge joins and it takes < 20 ms) but then I ended up adding 9 more indexes to a 20 million records table, even though I created the indexes as filtered to save some space and the query uses the indexes I'm still looking for other ways to make it run this fast without having to add the 9 indexes. I tried creating a single index with all the columns but it will inevitable cause an index scan and takes around 6000 ms of CPU, any recommendations?

Thank you

Best Answer

One thing you can try is re-writing the query to use the UNION ALL clause instead of a bunch of OR operators in the WHERE clause's predicate. It depends on the execution plan you were getting previously vs what this one generates though.

Related Question