Click or drag to resize

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.

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.

Categories

Performance Rules, New Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 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

 MessageLineColumn
1SA0238 : The user-defined function [fnGetPreviousDate] appearing in the query filter can cause performance problems.66
2SA0238 : The user-defined function [fnGetDate] appearing in the query filter can cause performance problems.126
3SA0238 : The user-defined function [fnGetDate] appearing in the query filter can cause performance problems.1831
4SA0238 : The user-defined function [ISOweek1] appearing in the query filter can cause performance problems.2281
See Also

Other Resources