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');

  }
 }

-
Published 17 January 2005 22:43 by simonsabin

Comments

03 June 2007 22:20 by SimonS' SQL Server Stuff

# Parallel processing in TSQL

I will be over in Ireland on Thursday presenting on Service Broker . It appears few people are...

07 June 2007 15:41 by SimonS SQL Server Stuff

# Parallel processing in TSQL

I will be over in Ireland on Thursday presenting on Service Broker . It appears few people are actually

29 September 2007 04:24 by Rickie

# Transact SQL User-Defined Functions in SQL Server 2005

Transact SQL User-Defined Functions in SQL Server 2005