SA0106 : Avoid OR operator in queries

The OR operator in SQL queries can lead to suboptimal query performance.

Description

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:

SQL
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.

How to fix

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:

SQL
 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:

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

Scope

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

Parameters
Name Description Default Value
IgnoreOrWithIsNull

The parameter can ignore OR operators which have one of their operand be IS NULL comparison expression.

yes

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
1 hour per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1CREATE PROCEDURE [dbo].testsp_SA00106
 2(
 3 @param1  nvarchar(20),
 4 @param2  nvarchar(20),
 5 @param3  nvarchar(20)
 6)
 7AS
 8
 9SELECT BusinessEntityID, Name 
10FROM Sales.Store
11WHERE (BusinessEntityID LIKE @param1 OR SalesPersonID LIKE @param3) AND
12      (Name LIKE @param2 OR @param2 IS NULL);

Analysis Results
  Message Line Column
1 SA0106 : Avoid OR operator in queries. 11 37
See Also

Other Resources