SA0238 : The user-defined function appearing in the query filter can cause performance problems
Introduction
Section titled “Introduction”Ensure user-defined functions in WHERE clause or JOIN conditions reference table columns effectively.
Description
Section titled “Description”When using user-defined functions in the WHERE clause or JOIN conditions, a common problem arises if these functions return a deterministic or constant result and do not depend on table columns. Such functions can be evaluated before the query executes. However, if they are not schema-bound, SQL Server evaluates them for every row, leading to performance inefficiencies.
For example:
-- Example of inefficient use in a WHERE clauseSELECT * FROM Users WHERE SomeFunction() = 1;In this example, SomeFunction() doesn’t depend on any columns from the Users table. If it’s not schema-bound and is used within a query, SQL Server may evaluate it for each row, which is unnecessary and slows down query execution.
-
Increased execution time as the function is evaluated row by row.
-
Potential bottleneck due to unnecessary computational overhead.
How to fix
Section titled “How to fix”Optimize performance by extracting function calls from the WHERE clause and assigning them to local variables.
Follow these steps to address the issue:
1.Identify user-defined function calls in the WHERE clause or JOIN conditions that do not depend on table columns.
2.Declare a local variable to store the function’s result outside the query using a DECLARE statement. This way, the function is evaluated only once.
3.Assign the result of the function call to the local variable before executing the query.
4.Modify the original query to replace the function call in the WHERE clause or JOIN condition with the local variable.
For example:
-- Example of corrected queryDECLARE @Result INT;SET @Result = SomeFunction();
SELECT * FROM Users WHERE @Result = 1;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.
Effort To Fix
Section titled “Effort To Fix”1 hour 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” SELECT OrderId, ProfileId, OrderNumber, CreatedFROM Orders oWHERE fnGetPreviousDate() > Created
SELECT OrderId, OrderNumber, CreatedFROM OrdersWHERE fnGetDate(getdate()) > Created OR DATEADD(day,2,getdate()) > getDate()
DECLARE @Date datetimeSET @Date = GetDate()
SELECT * FROM dbo.Orders WHERE fnGetDate(@Date) > Createdselect * from Production.Product where '00000' + ProductNumber = ufnLeadingZeros(12)select * from Production.Product where '00000' + ProductNumber = dbo.ufnLeadingZeros(12)
select * from Purchasing.PurchaseOrderHeader where dbo.ISOweek1(OrderDate) = dbo.ISOweek1(getdate())Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0238 : The user-defined function [fnGetPreviousDate] appearing in the query filter can cause performance problems. | 6 | 6 |
| 2 | SA0238 : The user-defined function [fnGetDate] appearing in the query filter can cause performance problems. | 12 | 6 |
| 3 | SA0238 : The user-defined function [fnGetDate] appearing in the query filter can cause performance problems. | 18 | 31 |
| 4 | SA0238 : The user-defined function [ISOweek1] appearing in the query filter can cause performance problems. | 22 | 81 |