Optimize performance for query with lots of OR's

Question

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

Answer 1

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.

Answer 2

If you dont have an index i am not sure then is point in rewriting the query. By definition, if there is no covering index, the server must do a full index scan and compare every row.

So in case there is no covering index, rewrite will not help.

What may help, is indexed view if it is possible to use it instead.