tomerlev


Hi,

I have a query which goes like the following:

select q.uin, datediff(YY, isnull(m.birthdate, '1900-01-01'), '20070220'), m.gender

from (select top 100000 cu.uin

from dbo.snap_stat_log_tb cu) q

left join reporting..Member_Directory m on q.uin = m.uin

I have took the:

datediff(YY, isnull(m.birthdate, '1900-01-01'), '20070220')

from this query and put it inside a UDF.

the UDF goes like this:

create FUNCTION [dbo].[fn_ageold]

(@birth_date AS DATETIME, @event_date AS DATETIME)

RETURNS INT

AS

BEGIN

DECLARE @vAge INT

IF @birth_date >= @event_date

RETURN 0

SET @vAge = DATEDIFF(YY, @birth_date, @event_date)

RETURN @vAge

END

Now, when I call my query with the UDF:

select q.uin, dbo.fn_ageold(isnull(m.birthdate, '1900-01-01'), '20070220'), m.gender

from (select top 100000 cu.uin

from dbo.snap_stat_log_tb cu) q

left join reporting..Member_Directory m on q.uin = m.uin

I get performance problems!!

the first query get me results in 3 seconds; while calling the second query with the UDF on the same amount of records take lots of time

Can someone advice me on that

thank you,

Tomer




Re: UDF question

bass_player


Can you check the execution plan when you execute your query and see if what is causing this





Re: UDF question

Waldrop

I would also suggest that there is enough overhead associated with scalar UDFs to reconsider.  Leave at least reasonably simple code as inline code rather than an abstraction as a scalar function when it remains readable.  I would also suggest this is one of those cases; reserve translation to a scalar UDF when you are willing to make a tradeoff of some performance for readability.

After I thought about it the problem with scalar UDFs is frequently the way they inhibit the optimizer's ability to optimize -- and that might be the case here.






Re: UDF question

tomerlev

Hi,

thank you guys for your response. What I have done is that I saw at the execution plan that the SQL server acts differently with the UDF (Merge join) and without it (HASH). Then I added to the query the OPTION(RECOMPILE) statment in order to tell the optimizer to recompile this query and to ignore its execution plan.

After doing that, the execution time dramatically changed and I got the response with the UDF in the query after 2 sec.

I guess that in some way both of you were right with the direction of checking the issue as it concern the eceution plan and inline code also.

thank you both on your time,

Tomer





Re: UDF question

Roji. P. Thomas

Completely agree with Waldrop.

Here the udf has to be invoked for each row, which contributes to the performance problem you are facing.