UDF as UDF parameter performance hit

Question

Post reply

UDF as UDF parameter performance hit

Jim Russell-390299

SSCrazy Eights

Points: 9333
More actions
November 20, 2007 at 12:11 pm

#184000

To return views tailored to the user, I have a simple users table that holds user IDs, view names, parameter names, and parameter values that are fetched based on SUSER_SNAME(). The UDF is called MyParam, and takes as string arguments, the name of the view in use, and a parameter name. (The view the user sees is really a call to a corresponding table returning UDF, which accepts some parameters corresponding to the user.)
But the performance is very dependent on the nature of the function call. Here are two samples and the numbers reported by (my first use of) the performance monitor:
Call to table returning UDF, using local variables:
declare @orgauth varchar(50)
set @orgauth = dbo.MyParam('DeptAwards', 'OrgAuth')
declare @since datetime
set @since = DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP)
select * from deptAwardsfn(@orgauth,@since)
[187 CPU, 16103 Reads, 187 Duration]
Call to same table returning UDF, using scalar UDFs in parameters:
SELECT *
from deptAwardsFn (
dbo.MyParam('DeptAwards', 'OrgAuth')
,DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP)
)
[20625 CPU, 1709010 Reads, 20632 Duration]
(My BOL documentation claims the CPU is in milliseconds and the Duration is in microseconds -- which I question.) Regardless of the unit of measure, it takes a whole bunch longer in the second case.
My only guess is that T-SQL is deciding that the parameter values (returned by dbo.MyParam) are nondeterministic, and continually reevaluates them somehow or other. (What ever happened to call by value?)
Can anyone shed some light on this strange (to me) behavior?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

I believe you've hit the nail on the head... not sure even a "by value" would help on such a recursive call...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 2

Recursive? Are you seeing something I'm too dumb to realize?

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

I'd say that your approach was probably not the most efficient way to do things - your second call executes 1.7 million page io - I wouldn't want calls like that on a prod server - forget duration -it's affected by far too many other factors - io is always a true reading and 1.7 million is not good.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 4

For sure, but the big question is why is there such a big difference between the two call styles? (I have since discovered that the reference to CURRENT_TIMESTAMP in the function argument is the cause, but I suspect that is an error -- it should only capture the value of CURRENT_TIMESTAMP once, when making the function call IMHO.)