Performance Benefits of using Expression over User Defined Functions
Summary
No matter how simple a UDF is, there's a large performance penalty paid when they're used. This penalty shows up as poor query execution time when a query applies a UDF to a large number of rows, typically 1000 or more. The penalty is incurred because the SQL Server database engine must create its own internal cursor like processing. It must invoke each UDF on each row. If the UDF is used in the WHERE clause, this may happen as part of the filtering the rows. If the UDF is used in the select list, this happens when creating the results of the query to pass to the next stage of query processing. It's the row by row processing that slows SQL Server the most. In many cases an expression can replace the functionality that the UDF provides and offer a significant performance benefit over a UDF (see examples below).
More Information
SETUP FOR THE TEST
======================
--- Create Test Table
CREATE TABLE UDF_parameters (param1 int, param2 int, param3 int, param4 int)
-- Create Clustered Index
CREATE CLUSTERED INDEX CLU1 ON UDF_parameters(Param4)
--Code for the UDF.
CREATE FUNCTION dbo.divide_func(@numerator as int, @denominator as int, @default as float)
RETURNS float
BEGIN
if @denominator = 0
RETURN @default
if @numerator = 0
RETURN @default
RETURN @numerator/@denominator
END
--- Insert records in the table ----
declare @count int
set @count =1 -----> This will insert 1 million records to the table
while @count <1000001
begin
INSERT INTO UDF_parameters values(@count+3,@count+2,@count+1, @count)
set @count = @count+1
end
Using UDF in Query SELECT list
SET STATISTICS TIME ON
GO
select PARAM1, DEVIDED_VALUE = dbo.divide_func(param2,param3,CAST(param4 as float)), param4 from UDF_parameters
GO
SET STATISTICS TIME OFF
GO
Rows Executes StmtText
------ -------- ---------------------------------------------------------------------------------------------------------------
500000 1 select PARAM1, DEVIDED_VALUE = dbo.divide_func(param2,param3,CAST(param4 as float)), param4 from UDF_parameters
500000 1 |--Compute Scalar(DEFINE:([Expr1004]=[TEST1].[dbo].[divide_func](parameter... )
500000 1 |--Clustered Index Scan(OBJECT:([TEST1].[dbo].[UDF_parameters].[CLU1]))
If you notice the plan, we would see that the Function call is being made, for all the rows being returned by the Clustered Index Scan.
If you take a profiler trace while running this statement, you would notice the multiple executions of the UDF. (Image at the end of the Page)
Query with UDF in Where Clause
SET STATISTICS TIME ON
GO
select PARAM1,param2,param3, param4 from UDF_parameters where dbo.divide_func(param2,param3,CAST(param4 as float)) = 1
GO
SET STATISTICS TIME OFF
GO
Rows Executes StmtText
------ -------- -------------------------------------------------------------------------------------------------------------------------------------------------------------500000 1 select PARAM1,param2,param3, param4 from UDF_parameters where dbo.divide_func(param2,param3,CAST(param4 as float)) = 1
500000 1 |--Filter(WHERE:([TEST1].[dbo].[divide_func]([TEST1].[dbo].[UDF_parameters].[param2],[TEST1].[dbo].[UDF_parameters].[param3],CONVERT(float(53),[TEST1].[dbo].[UDF_parameters].[param4],0))=(1.000000000000000e+000)))
500000 1 |--Clustered Index Scan(OBJECT:([TEST1].[dbo].[UDF_parameters].[CLU1]))
Notice a similar behaviour in multiple executions of the UDF, when we have the UDF in the where clause. (Image at the end of the Page)
Using Expression In Query
SET STATISTICS TIME ON
GO
SELECT PARAM1,
DEVIDED_VALUE =
CASE
WHEN PARAM3=0 THEN param4
WHEN param2=0 THEN param4
ELSE param2/param3
END ,
PARAM4
from UDF_parameters
GO
SET STATISTICS TIME OFF
GO
Rows Executes StmtText
------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
500000 1 SELECT PARAM1,DEVIDED_VALUE = CASE WHEN PARAM3=0 THEN param4 WHEN param2=0 THEN param4 ELSE param2/param3 END ,PARAM4 from UDF_parameters
0 0 |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [TEST1].[dbo].[UDF_parameters].[param3]=(0) THEN [TEST1].[dbo].[UDF_parameters].[param4] ELSE CASE WHEN [TEST1].[dbo].[UDF_parameters].[param2]=(0) THEN [TEST1].[dbo].[UDF_parameters].[param4] ELSE [TEST1].[dbo].[UDF_parameters].[param2]/[TEST1].[dbo].[UDF_parameters].[param3] END END))
500000 1 |--Clustered Index Scan(OBJECT:([TEST1].[dbo].[UDF_parameters].[CLU1]))
Notice, how using a case expression, has only on execution.
Comparison of the time taken by the Different Statements.
Rows Returned
WITH UDF in Select List
UDF in Where Clause
With Expression
0.5 Million
26 seconds
26 seconds
4 seconds
1.0 Million
52 seconds
52 seconds
8 seconds
1.5 Million
86 seconds
87 seconds
12 seconds
Snapshots of Profiler Traces
==============================
As we can see, the query when using the function is slower than the one using the CASE expression.
In this case I was using very simple parameters, but if we have some complex parameters (like those involving mathematical operation) we would see the response time increase even more.
by
Sourabh Agarwal
Technical Lead, Microsoft SQL Server