Improving Query performance which contains "OR"

I have noticed quite a few times that people overlook the performance issues related to using “OR” operators in their SQL queries.

Suppose you have a following query

Select ProductId, Name,Description from tblProducts
where
Name=‘Keyed Washer’ OR Description =‘Assembly Item’

and the only index you have is on ‘Name’ column. In this scenario, SQL Server will not use any index and will perform table scan which may result in poor query performance depending on the size of table.

Above query can be optimized by rewriting it as follows:

Select ProductId, Name,Description from tblProducts
where Name=‘Keyed Washer’
Union All
Select ProductId, Name,Description from tblProducts
where Description =‘Assembly Item’

Note the usage of “Union All” here. This query has following benefits:

1. Since these are two independent queries joined together by Union All operator therefore, SQL Server will use index in first query at least.

2. Union All just combines two queries and does not skip any duplicate row so it will give exactly the same number of rows which you were getting before but with improved system response.

3. Future query tuning is easy because of modular approach in the rewritten query. You can add other indexes to improve performance as and when necessary.