UDF as UDF parameter performance hit

Question

UDF as UDF parameter performance hit

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

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

Jeff Moden SSC Guru Points: 1004664 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.)