SA0238 : The user-defined function appearing in the query filter can cause performance problems
The topic describes the SA0238 analysis rule.
The user-defined function appearing in the query filter can cause performance problems
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.
The rule has a Batch scope and is applied only on the SQL script.
Rule has no parameters.
The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.
Performance Rules, New Rules
There is no additional info for this rule.
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())
|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|
© Ubitsoft Ltd. All Rights Reserved.