Performance of user defined functions

Question

Performance of user defined functions

Torres-682756

SSC Enthusiast

Points: 197
More actions
April 3, 2009 at 1:17 pm

#155508

Hello.
When joining a table with a udf, does the Server process the udf query independently, or does it consider the joins and any where conditions?
I'm wondering if I should be adding common join fields as parameters in my udf's.
For example, would "myudf()" below ignore "RegionID=1" until after it's gathered it's records, or include it in it's gathering?
Select
[select fields...]
from
Persons
inner join
myudf( [parameters..] ) udf on udf.RegionID = Persons.RegionID
where
Persons.RegionID = 1

Viewing 10 posts - 1 through 9 (of 9 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

It'll eliminate rows before running the UDF.

The main problem with UDF performance is that the query optimizer has a rough time estimating rows they'll return, so it assumes it'll be 1 row, and that can kill performance.

Edit: Almost forgot to mention, you might want to take a look at Cross Apply in Books Online. It can be more efficient than just joining to a UDF, and definitely gives you more options with them.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Matt Simmons SSCrazy Points: 2846 More actions · Answer 2

Run your sample query and turn on the execution plan. Review the plan and see at what point the where clause is used.

Regards,
Matt

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 3

of course that also depends on what your udf is doing !

is it just a split/string function or does it perform access to other objects ?

if you post it we may be able to figure it out.

Don't test with small tables .....

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Torres (4/3/2009)
Hello.
When joining a table with a udf, does the Server process the udf query independently, or does it consider the joins and any where conditions?

Depends. If it's a multi-statement table valued function it's run independently. If it's an inline table valued function then the defintion of the function is expanded into the query (much like a view) and it's all run together.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Torres-682756 SSC Enthusiast Points: 197 More actions · Answer 5

Thanks for the replies.

The udf is a Table-valued Function, returning a dataset.

I could add the RegionID as a parameter to the udf, but was wondering if that was necessary.

Thanks.

Torres

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Torres (4/3/2009)
The udf is a Table-valued Function, returning a dataset.

Multistatement or inline?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Torres-682756 SSC Enthusiast Points: 197 More actions · Answer 7

Sorry Gail. It's a single Inline function with a subquery.

I assumed this was a common option with most udfs. Or not?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Torres (4/3/2009)
I assumed this was a common option with most udfs. Or not?

Well, there's two different types of table valued udf, and they follow completely different rules.

The inline table valued function looks kinda like this, and when run it's definition is expanded inline into the query. Hence filters in the where clause may be pushed down into the function.

CREATE FUNCTION Func (@Param1 int) AS

RETURN (SELECT Somecolumns .....)

The second is a multistatement udf and in that case the function is run separately, the results stored in a table variable and that variable is used ion the rest of the query. It looks kinda like this

CREATE FUNCTION Func (@Param1 int) AS

RETURNS TABLE (Table definition here

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Torres (4/3/2009)
Thanks for the replies.
The udf is a Table-valued Function, returning a dataset.
I could add the RegionID as a parameter to the udf, but was wondering if that was necessary.
Thanks.
Torres

I'd be curious to see the code for the function and the query that uses it.

--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)