How many times UDF is called

Question

Post reply

How many times UDF is called

krishnaroopa

SSChasing Mays

Points: 650
More actions
November 30, 2010 at 2:18 am

#229689

Suppose i have a scalar value udf that returns 1 or 0, udf_checkCondition( string condition).
I am using the udf in the following query's where clause, along with other conditions. How it will be executed and how many times the UDF will be called.
select * from table1
where c1 = @c1 and c2 = @c3 and udf_checkCondition(c3) = 1
Suppose the table has 1000 records., Applying first two conditions, only 2 or 3 rows matches. Will the UDF be called for all rows or only filtered rows.
please help me to decide on using UDF?

Viewing 12 posts - 1 through 11 (of 11 total)

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

Abhijit More SSCertifiable Points: 6897 More actions · Answer 1

Hello,

As you'r using it in Search Criteria the UDF will be called for all records for matching the search criteria.

Abhijit - http://abhijitmore.wordpress.com

Brigadur SSCrazy Points: 2097 More actions · Answer 2

Hi,

As a column is parameter to the function, there is logically no other way, but to execute it for all rows. No intelligence in the optimizer, in my opinion, can change that.

Cheers

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

It depends on the execution plan that the optimiser picks.

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

Brigadur SSCrazy Points: 2097 More actions · Answer 4

GilaMonster (11/30/2010)
It depends on the execution plan that the optimiser picks.

Hi Gail. Could you explain it a little more detailed for this particular case? That would be interesting.

Thanks

Istvan Kathi

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

If there's an index on c1 and/or c2 then SQL may decide to seek on one (or both) and evaluate the udf only on the rows returned from them. Or it may decide to scan the cluster/heap in which case the udf will be evaluated on every row.

As I said, it depends. There's no where near enough information available to state an absolute answer here. To say with any degree of confidence we'd need to see the available indexes and cardinality values.

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

Brigadur SSCrazy Points: 2097 More actions · Answer 6

Brigadur

SSCrazy

Points: 2097

November 30, 2010 at 8:55 am

#1256832

Thank you Gail! 🙂

Cheers

Istvan

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

The only thing that matters here is the OP's request: "please help me to decide on using UDF?"

I will help you with that: NEVER, EVER, EVER USE SCALAR UDFs.

There, post answered definitively. 😀

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

tfifield SSCrazy Eights Points: 9655 More actions · Answer 8

Here's my 2 cents. I disagree with Kevin on this. Scalar functions can be very useful to encapsulate logic - especially for string handling and formatting like phone numbers. Using the function means that if you change the format for something it only has to be changed in one place.

Scalar functions will kill your performance if they either access a table in order to return the value (RBAR) or if they are used as part of a WHERE clause since they aren't SARGable. As with Gail's post the optimizer may quite likely use a SARGable argument before it gets to the UDF - it may not.

So as a rule of thumb - don't use them in a WHERE clause and don't use them to look up data from another table and return it. Either one will kill your performance.

Todd Fifield

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 9

TheSQLGuru (11/30/2010)
The only thing that matters here is the OP's request: "please help me to decide on using UDF?"
I will help you with that: NEVER, EVER, EVER USE SCALAR UDFs.
There, post answered definitively. 😀

"There is always an easy solution to every human problem—neat, plausible, and wrong."

- H. L. Mencken, New York Evening Mail, November 16, 1917

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

tfifield (12/1/2010)
Here's my 2 cents. I disagree with Kevin on this. Scalar functions can be very useful to encapsulate logic - especially for string handling and formatting like phone numbers. Using the function means that if you change the format for something it only has to be changed in one place.
Scalar functions will kill your performance if they either access a table in order to return the value (RBAR) or if they are used as part of a WHERE clause since they aren't SARGable. As with Gail's post the optimizer may quite likely use a SARGable argument before it gets to the UDF - it may not.
So as a rule of thumb - don't use them in a WHERE clause and don't use them to look up data from another table and return it. Either one will kill your performance.
Todd Fifield

There are other ways in which scalar UDFs can harm performance. Chiefly among them is preventing parallelism in the query plan.

I will stick by my statement simply because I can count on one hand the number of scalar UDFs I have seen at clients that are acceptable and cannot possibly count the messes I have cleaned up from their use. It is by far the single biggest problem I have had to deal with in 10+ years of SQL Server consulting.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

tfifield SSCrazy Eights Points: 9655 More actions · Answer 11

Kevin,

I will stick by my statement simply because I can count on one hand the number of scalar UDFs I have seen at clients that are acceptable and cannot possibly count the messes I have cleaned up from their use. It is by far the single biggest problem I have had to deal with in 10+ years of SQL Server consulting.

I'd kind of forgotten about the parallelism thing. I still use them when they don't affect performance - e.g. I'm doing something serialized anyhow. They're still great for formatting phone numbers.

Todd Fifield