SA0238 : The user-defined function appearing in the query filter can cause performance problems

The topic describes the SA0238 analysis rule.

Message

The user-defined function appearing in the query filter can cause performance problems

Description

The rule checks T-SQL code for user-defined functions, which do not reference table columns and appear in WHERE clause or JOIN conditions.

If the function result is deterministic or a constant value, and does not depend on table columns, it can be evaluated before executing the query.

This is important, because even if the function is deterministic, in case it is not schema bound, SQL Server will execute it for each row before filtering the results.

How to fix

To resolve the problem, extract the function calls made in the WHERE clause into local variable.

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix
1 hour per issue.
Categories

Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1 SELECT OrderId,
 2       ProfileId,
 3       OrderNumber,
 4       Created
 5FROM Orders o
 6WHERE fnGetPreviousDate() > Created 
 7
 8SELECT OrderId,
 9       OrderNumber,
10       Created
11FROM Orders
12WHERE fnGetDate(getdate()) > Created
13        OR DATEADD(day,2,getdate()) > getDate()
14
15DECLARE @Date datetime
16SET @Date = GetDate()
17
18SELECT * FROM dbo.Orders WHERE fnGetDate(@Date) > Created
19select * from Production.Product where '00000' +   ProductNumber  =  ufnLeadingZeros(12)
20select * from Production.Product where '00000' +   ProductNumber  =  dbo.ufnLeadingZeros(12)
21
22select * from Purchasing.PurchaseOrderHeader where dbo.ISOweek1(OrderDate) = dbo.ISOweek1(getdate())
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
See Also

Other Resources

Analysis Rules