Demo Performance Penalty Of User Defined Functions

SQL Server User defined functions can be a powerful tool, but they can also create a substantial performance penalty in a query where they are called a large number of times. Sometimes it is something that must be accepted to get the job done, but there is often an alternative of putting the code from the function “in-line� in a SQL query. That also has a penalty in development time, so judgment about which way to go is called for.

I did some testing on three different methods of converting combinations of integer values of Year, Month, Day, Hour, Minute, and Second to Datetime values and compared the runtime of each. In the first method, I did the conversion in-line in the query. In the second method, I used a UDF to do the conversion using the same algorithm as the in-line query. In the third method, I used a UDF that called two more UDFs to do the conversion.

To perform the test, I loaded a table with 3,999,969 randomly generated date/times, along with the matching year, month, day, hour, minute, and seconds, in the range of 1753-01-01 to 9999-12-31. I re-indexed the table with fill factor of 100 to make the physical size as small as possible.

For the actual test, I ran queries that converted the year, month, day, hour, minute, and second on each row to a datatime, and compared it to the datetime from that row. I ran the query using the in-line conversion, single UDF (DateTime1), and with the UDF (DateTime2) that called two more UDFs (Date and Time). I ran the tests several times, and saw only minor variations in run time. The single UDF took over 8 times as long to run as the in-line conversion. The test with the UDF that called other UDFs took over 36 times as long to run as the in-line conversion, and took over 4 times as long to run as the single UDF.

These results show that there can be a substantial performance penalty for using a UDF in place of in-line code, and that UDFs that call other UDFs can also have a substantial performance penalty compared to a UDF that does not call other UDFs.




Code to load table with test data. The functions used in the script to load the test data can be found on these links:
Random Datetime Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69499
Number Table Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685


create table T_DATE (
[DATE] datetime not null primary key clustered ,
[YEAR]smallint not null ,
[MONTH]tinyint not null ,
[DAY]tinyint not null ,
[HOUR]tinyint not null ,
[MINUTE]tinyint not null ,
[SECOND]tinyint not null
)

insert into T_DATE
select distinct top 100 percent
[DATE] = dateadd(ms,-datepart(ms,a.[DATE]),a.[DATE]),
[YEAR]= year(a.[DATE]),
[MONTH]= month(a.[DATE]),
[DAY]= day(a.[DATE]),
[HOUR]= datepart(hour,a.[DATE]),
[MINUTE]= datepart(minute,a.[DATE]),
[SECOND]= datepart(second,a.[DATE])
from
(
selecttop 100 percent
[DATE] =
[dbo].[F_RANDOM_DATETIME]( '17530101', '99991231',newid() )
from
f_table_number_range(1,4000000) aa
order by
1
) a
order by
a.[DATE]

dbcc dbreindex(T_DATE,'',100)

exec sp_spaceused 'T_DATE','true'

select count(*) from T_DATE



Code to create functions used in the test. These functions are based on functions that Jeff posted in his blog on this link, modified with some suggestions of mine:
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx


create function DateTime1
(@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
-- returns a dateTime value for the date and time specified.
returns datetime
as
begin
returndateadd(month,((@Year-1900)*12)+@Month-1,
dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,@Day-1))
end
go
create function Date(@Year int, @Month int, @Day int)
-- returns a datetime value for the specified year, month and day
returns datetime
as
begin
return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
end
go

create function Time(@Hour int, @Minute int, @Second int)
-- Returns a datetime value for the specified time at the "base" date (1/1/1900)
returns datetime
as
begin
return dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,0)
end
go
create function DateTime2
(@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
-- returns a dateTime value for the date and time specified.
returns datetime
as
begin
return dbo.Date(@Year,@Month,@Day) + dbo.Time(@Hour, @Minute,@Second)
end
go



Test code:


set nocount on
go
select [T_DATE Rowcount] = count(*) from T_DATE
go
declare @count int
declare @st datetime
select @st = getdate()

select
@count = count(*)
from
T_DATE a
where
a.[DATE] <> 0+a.[DATE]

select [MS No Action] = datediff(ms,0,getdate()-@st)
go
declare @count int
declare @st datetime
select @st = getdate()

select
@count = count(*)
from
T_DATE a
where
a.[DATE] <>
dateadd(month,((a.YEAR-1900)*12)+a.MONTH-1,
dateadd(ss,(a.HOUR*3600)+(a.MINUTE*60)+a.SECOND,a.DAY-1))

select [MS No Function] = datediff(ms,0,getdate()-@st)
go
declare @count int
declare @st datetime
select @st = getdate()

select
@count = count(*)
from
T_DATE a
where
a.[DATE] <> dbo.DateTime1(a.YEAR,a.MONTH,a.DAY,a.HOUR,a.MINUTE,a.SECOND)

select [MS DateTime1] = datediff(ms,0,getdate()-@st)
go
declare @count int
declare @st datetime
select @st = getdate()

select
@count = count(*)
from
T_DATE a
where
a.[DATE] <> dbo.DateTime2(a.YEAR,a.MONTH,a.DAY,a.HOUR,a.MINUTE,a.SECOND)

select [MS DateTime2] = datediff(ms,0,getdate()-@st)
go



Sample test results:


T_DATE Rowcount
---------------
3999969

MS No Action
------------
1773

MS No Function
--------------
9923

MS DateTime1
------------
82213

MS DateTime2
------------
357683






CODO ERGO SUM