User Defined Function performance comparison
For some time now I wanted a basic example that showed the impact of using functions (UDFs), I was looking at SQLServerCentral
and saw a new piece on an LPAD function
http://www.sswug.org/see/Lpad_function_in_T-SQL-20878
So I've simplified it slightly and put it in a loop so see the difference between calling the code directly or using the function, I have also added a CLR version.
The results are as follows
It is interesting that the TSQL udf is 6-7 times slower than using the TSQL system functions directly. Thats quite a hit for having more mangeable code. What surprised me was that calling the CLR function was quicker than the TSQL UDF but still quite a hit.
A number of things to point out,
- This is a very simple function, for more complicated functions the results will be different. (MS report CLR functions can be quicker than using TSQL when complex)
- The times are for 100000 iterations and so for 1 call the speed is still fast
- However if you are running a high performance system the last point is irrelevant, you will be able to process 5 times more calls using the system functions directly than you can using a UDF.
- A set based query involving a UDF may result in that UDF being called for each row in the query, and thus severly hinder performance
So in summary always be careful about using UDFs, assess there usage before you implement them. As a means of tidying up code they are good but you can't have your cake and eat it, there may be a performance hit for using them .
Simon
---Code---
drop function TSQLlpad
go
create function TSQLlpad(@str varchar(255))
returns varchar(500)
as
begin
declare @v as varchar(500)
set @v = replicate ('0',6-len(@str)) + @Str
return @v
end
go
declare @i int
declare @str varchar(500)
declare @s datetime
declare @v varchar(500)
set @str = '1234'
set @i = 0
set @s = getdate()
while @i < 100000
begin
set @v = dbo.TSQLlpad(@str)
set @i = @i + 1
end
select 'result using udf',datediff (ms,@s,getdate()),@i
go
declare @i int
declare @str varchar(500)
declare @s datetime
declare @v varchar(500)
set @str = '1234'
set @i = 0
set @s = getdate()
while @i < 100000
begin
set @v = dbo.CLRlpad(6,@str)
set @i = @i + 1
end
select 'result using CLR Function', datediff (ms,@s,getdate()),@i
go
declare @i int
declare @str varchar(500)
declare @s datetime
declare @v varchar(500)
set @str = '1234'
set @i = 0
set @s = getdate()
while @i < 100000
begin
set @v = replicate ('0',6-len(@str)) + @Str
set @i = @i + 1
end
select 'Result using TSQL Functions',datediff (ms,@s,getdate()),@i
go
public sealed class simon
{
public static SqlString LPad(int len, SqlString var)
{
string value;
value = (string)var;
return (SqlString)value.PadLeft((int)len,'0');
}
}
-