SA0028 : Function call can be extracted from the WHERE clause to avoid unnecessary table scan
Introduction
Section titled “Introduction”Minimize unnecessary function calls in the WHERE clause to improve query performance.
Description
Section titled “Description”The WHERE clause is crucial for filtering results and optimizing query performance. However, using function calls that do not depend on table columns within the WHERE clause can lead to inefficiencies. Such function calls might force the SQL Server optimizer to conduct unnecessary table scans, impacting performance negatively.
Example of a query with a problematic function call in the WHERE clause:
SELECT * FROM Employees WHERE YEAR(GETDATE()) = YearHired;In this example, the function YEAR(GETDATE()) is invariant-it does not rely on any data from the database tables. By placing it in the WHERE clause, the optimizer may execute this function repeatedly for each row, increasing execution time, especially with large datasets. A better approach would be extracting this function call into a variable before executing the query, thus reducing overhead.
-
Decreased query performance due to repeated function execution.
-
Increased likelihood of unnecessary table scans by the SQL optimizer.
How to fix
Section titled “How to fix”Optimize T-SQL queries by minimizing unnecessary function calls in the WHERE clause to enhance performance and prevent inefficient index usage.
Follow these steps to address the issue:
1.Identify function calls within the WHERE clause that do not depend on any table columns, such as YEAR(GETDATE()) .
2.Move these invariant function calls out of the WHERE clause by calculating their results once and storing them in variables or constants.
3.Use these variables in the WHERE clause to compare against table columns, which will reduce repetitive function execution.
For example:
-- Before optimizationSELECT * FROM Employees WHERE YEAR(GETDATE()) = YearHired;
-- After optimizationDECLARE @CurrentYear INT;SET @CurrentYear = YEAR(GETDATE());
SELECT * FROM Employees WHERE @CurrentYear = YearHired;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”| Name | Description | Default Value |
|---|---|---|
| IgnoreFunctionsList | Comma separated lists of functions which to be ignored. | GetDate |
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”5 minutes per issue.
Categories
Section titled “Categories”Performance Rules, Bugs
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”-- The call to the CHECKSUM function can be avoided by setting its result to a variable which later to be used in the WHERE clauseSELECT *FROM Production.ProductWHERE CHECKSUM( N'Bearing Ball' ) = cs_Pname AND Name = N'Bearing Ball';
DECLARE @Checksum AS INT
SET @Checksum = CHECKSUM( N'Bearing Ball' )
SELECT *FROM Production.ProductWHERE @Checksum = cs_Pname AND Name = N'Bearing Ball';Analysis Results
Section titled “Analysis Results”No violations found.