SA0106 : Avoid OR operator in queries

Using the OR operator in SELECT, UPDATE, and DELETE statements may cause issues in generating efficient query execution plans. SQL Server might struggle to optimize the use of OR, potentially leading to slower query performance and increased resource usage.

For example:

1-- Example of problematic query using OR
2SELECT * FROM Employees WHERE DepartmentId = 1 OR LocationId = 2;

This query can be problematic because SQL Server may create a less efficient query plan that scans more rows than necessary. This inefficiency can occur due to the optimizer’s difficulty in properly estimating the cost of combining multiple predicates with OR. Strategies such as query rewriting, using UNION, or additional indexing might be needed for more efficient execution.

• Reduces query performance and speed due to inefficient execution plans.

• Increases resource consumption, impacting the overall system performance.

Optimize the use of the OR operator in SQL queries to enhance performance and reduce resource consumption.

Follow these steps to address the issue:

1. Check the query plan in SQL Server Management Studio (SSMS) for performance bottlenecks such as index scans or table spools. Ensure that columns intended for seeking are properly indexed and utilized in the queries.

2. Identify queries with optional search parameters using the OR operator, such as WHERE (Col1 = @Param1 AND @Param1 IS NOT NULL) OR (Col2 = @Param2 AND @Param2 != ). Consider dynamically constructing SQL strings based on actual input parameters and execute with sp_executesql to optimize these queries.

3. Rewrite queries using UNIONs instead of OR to potentially improve performance, as UNIONs can make execution plans more efficient. Evaluate combinations or alternatives like multiple LEFT JOINs where applicable.

4. Avoid hard-coding parameter values into SQL strings. Use parameterized queries to take advantage of the query plan cache and avoid recompiling.

For example, using dynamic SQL to tailor queries:

 1CREATE TABLE #Tbl
 2(
 3    ID INT NOT NULL,
 4    Col1 VARCHAR(50) NOT NULL,
 5    Col2 VARCHAR(50) NOT NULL,
 6    PRIMARY KEY CLUSTERED (ID)
 7);
 8INSERT INTO #Tbl VALUES (1, 'abcd', '');
 9INSERT INTO #Tbl VALUES (2, '123', 'abc');
10DECLARE @Sql NVARCHAR(1000), @Param1 VARCHAR(50), @Param2 VARCHAR(50);
11SELECT @Param1 = '', @Param2 = 'abc';
12SET @Sql = N'SELECT ID FROM #Tbl WHERE 1=1' + 
13    CASE WHEN @Param1 != '' THEN ' AND Col1 = @Param1' ELSE '' END +
14    CASE WHEN @Param2 != '' THEN ' AND Col2 = @Param2' ELSE '' END;
15EXEC dbo.sp_executesql @Sql, N'@Param1 VARCHAR(50), @Param2 VARCHAR(50)', @Param1, @Param2;
16--DROP TABLE #Tbl;

In some cases, employ UNIONs for improved efficiency:

1SELECT ID FROM #Tbl WHERE Col1 = @Param1
2UNION ALL
3SELECT ID FROM #Tbl WHERE Col2 = @Param2;

The rule has a Batch scope and is applied only on the SQL script.

The rule does not need Analysis Context or SQL Connection.

Design Rules, Bugs

There is no additional info for this rule.

Other Resources