SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


User Defined Function Execution


User Defined Function Execution

Author
Message
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37922 Visits: 9274
Comments posted to this topic are about the item User Defined Function Execution

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5287 Visits: 3889
Um, question:
Shouldn't it be executing once per row in the FROM clause too?

Example:
SELECT * FROM dbo.MyTable T CROSS APPLY dbo.MyFunction(T.fielda)

Grazias!

Best Regards,

Chris Büttner
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37922 Visits: 9274
I used to think that too, but actually no. The FROM clause is the one place where the UDF doesn't execute once per row.

Remember that a UDF in the FROM clause, even using CROSS APPLY, is a table-defined function. Therefore, it is more set-based in nature than other functions and will not evaluate per each individual record, whereas a SUM() function or an AVG() function will evaluate every time a record is passed into the database engine.

Does that help?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5287 Visits: 3889
Hi Brandie,

thanks for your response.

I still am not sure whether your assumption is correct.
When reading BOL I get the assumption that the TVF is evaluated for each and every row:
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

http://technet.microsoft.com/en-us/library/ms175156.aspx

Thanks!

Best Regards,

Chris Büttner
StarNamer
StarNamer
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2774 Visits: 1992
Christian Buettner (2/13/2008)
Um, question:
Shouldn't it be executing once per row in the FROM clause too?

Example:
SELECT * FROM dbo.MyTable T CROSS APPLY dbo.MyFunction(T.fielda)

Grazias!

I think in this context the CROSS APPLY clause isn't counted as part of the FROM!

In http://msdn2.microsoft.com/en-us/library/ms175156.aspx (quoted by Christian above) Microsoft says that the UDF in the APPLY will execute once per row of the 'left input'.

Elsewhere, http://msdn2.microsoft.com/en-us/library/aa175085(SQL.80).aspx, it is indicated that the exact number of executions may be difficult to determine.
The number of times that a function specified in a query is actually executed can vary between execution plans built by the optimizer. An example is a function invoked by a subquery in a WHERE clause. The number of times the subquery and its function is executed can vary with different access paths chosen by the optimizer.

About the only certain thing is that
SELECT ... FROM udf

will execute the UDF once, and a UDF anywhere else will probably be executed at least once for each row returned, but may be executed many more times than that!

Derek.

Derek
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5287 Visits: 3889
Hello Derek,

I think in this context the CROSS APPLY clause isn't counted as part of the FROM!

Of course it is part of the FROM clause. APPLY is an operator that is used in the FROM clause. See BOL.

I totally agree that if you SELECT only from a TVF and don't join it to anything else, then it would be executed only once. But I cannot imagine that this (SELECT from TVF only) was the intention of the author.

Thanks for your comments though! Especially the hint with the optimizer.

Best Regards,

Chris Büttner
StarNamer
StarNamer
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2774 Visits: 1992
Hi Christian

I agree that APPLY must be counted as part of the FROM clause, since it's the only place it can be used.

So I think the correct answer should have been:
It executes only once if the UDF is in the FROM clause and not in an APPLY sub-clause and executes at least once per output row if it is located in any other clause of the query.
Obviously, it could execute many more times than the number of output rows, especially if a WHERE clause eliminates all the output. Smile

I suspect the author simply hadn't considered the APPLY sub-clause. Also the phrase "once per row" is very vague since it doesn't specify where the row is from.

Derek.

Derek
Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8968 Visits: 600
BOL says in the article "Creating User-defined Functions (Database Engine) ":
"The number of times that a function specified in a query is actually executed can vary between execution plans built by the optimizer. An example is a function invoked by a subquery in a WHERE clause. The number of times the subquery and its function is executed can vary with different access paths chosen by the optimizer."
So the real answer maybe not "one per row outside of FROM" but "whatever, more the one"


Regards,
Yelena Varshal

Matt Marston
Matt Marston
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2045 Visits: 412
I got the answer "correct" by choosing what I thought was the best choice out of the available options, but really, as Yelena pointed out, the answer is "it depends". The question didn't even address whether the function was scalar-valued or table-valued? CLR or not? One of the biggest things that it depends on is whether the function is deterministic (see User-defined Function Design Guidelines) and whether the values passed into the function are expressions involving only variables and literals or if it includes columns from the query.

If the system can determine both 1) that the function always returns the same value for the same input (it is deterministic) and 2) that the same values are passed into the function for every row (for a given execution of the query) then the function will be called once.

For example, assume that you have a user-defined function fnDateAdd that is equivalent to the built-in DATEADD function. Assume that a table with column OrderDate is specified in the FROM clause. Then consider the following to WHERE clauses:

WHERE fnDateAdd('day', 7, OrderDate) > '2008-02-13'



WHERE OrderDate > fnDateAdd('day', -7, '2008-02-13')



In the first case the values passed into the function vary for each row so the function has to be called for each row. In the second case the values passed into the function are the same for every row so it can be called once.
Greg Young
Greg Young
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 64
Doesn't anybody proof these questions? This is yet another example of where you have to guess what limitations the author had in mind. I tend to write UDFs that use column inputs (don't know why, just seems to work that way) so they always have to be evaluated on every row. If you don't specify the assumptions then no answer is truly correct and we're just throwing darts.

Regards,

Greg Young
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search