Performance and UDFs

Question

Post reply

Performance and UDFs

Steve Johnson

Default port

Points: 1404
More actions
December 8, 2003 at 2:26 pm

#160995

I have a select statement that calls this udf
(create function udfNextBusDayYr (@vDate smalldatetime)
RETURNS int
AS
begin
declare
@vYr integer,
@vDate2 smalldatetime,
@i integer
set @i = (select holiday from fiscalcal where convert(nvarchar(15),fdate,101) = convert(nvarchar(15),@vDate,101))
while @i = 1
begin
set @vdate2 =
(
select
case
when datepart(dw,@vDate) = 1 then
(select fdate
from fiscalcal
where convert(nvarchar(15),fdate,101) = convert(nvarchar(15),@vDate+1,101))
when datepart(dw,@vDate) = 2 then
(select fdate
from fiscalcal
where convert(nvarchar(15),fdate,101) = convert(nvarchar(15),@vDate+1,101))
when datepart(dw,@vDate) = 3 then
(select fdate
from fiscalcal
where convert(nvarchar(15),fdate,101) = convert(nvarchar(15),@vDate+1,101))
when datepart(dw,@vDate) = 4 then
(select fdate
from fiscalcal
where convert(nvarchar(15),fdate,101) = convert(nvarchar(15),@vDate+1,101))
when datepart(dw,@vDate) = 5 then
(select fdate
from fiscalcal
where convert(nvarchar(15),fdate,101) = convert(nvarchar(15),@vDate+1,101))
when datepart(dw,@vDate) = 6 then
(select fdate
from fiscalcal
where convert(nvarchar(15),fdate,101) = convert(nvarchar(15),@vDate+3,101))
when datepart(dw,@vDate) = 7 then
(select fdate
from fiscalcal
where convert(nvarchar(15),fdate,101) = convert(nvarchar(15),@vDate+2,101))
else
'12/1/1975'
end
)
set @vdate = @vdate2
set @i = (select holiday from fiscalcal where convert(nvarchar(15),fdate,101) = convert(nvarchar(15),@vDate,101))
end
set @vYr = (select yr from fiscalcal where convert(nvarchar(15),fdate,101) = convert(nvarchar(15),@vDate,101))
RETURN(@vYr)
end)
when I run the query without the udf I get 1700 rows in 2 sec when I add the udf back in it takes 38 sec. This is not a very complex udf, I really appreciate any input.
thanks
Steve Johnson

Steve Johnson

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

One reason might be that UDF are processed row by row which might be the reason that things are slowed down.

Have you compared the execution plan of both alternatives?

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Steve Johnson Default port Points: 1404 More actions · Answer 2

thanks for the input Frank I will take a look at the execution plan of both alternatives.

Steve Johnson