Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

User Defined Function Execution Expand / Collapse
Author
Message
Posted Tuesday, February 12, 2008 8:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:40 PM
Points: 7,210, Visits: 6,352
Comments posted to this topic are about the item User Defined Function Execution

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #454846
Posted Wednesday, February 13, 2008 12:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:05 AM
Points: 2,842, Visits: 3,875
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
Post #454879
Posted Wednesday, February 13, 2008 4:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:40 PM
Points: 7,210, Visits: 6,352
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #454945
Posted Wednesday, February 13, 2008 4:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:05 AM
Points: 2,842, Visits: 3,875
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
Post #454954
Posted Wednesday, February 13, 2008 5:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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
Post #454969
Posted Wednesday, February 13, 2008 5:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:05 AM
Points: 2,842, Visits: 3,875
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
Post #454972
Posted Wednesday, February 13, 2008 7:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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. :)

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
Post #455074
Posted Wednesday, February 13, 2008 9:10 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:12 PM
Points: 3,475, Visits: 582
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

Post #455153
Posted Wednesday, February 13, 2008 11:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:06 AM
Points: 1,921, Visits: 396
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.
Post #455244
Posted Wednesday, February 13, 2008 12:28 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 8, 2008 11:33 AM
Points: 332, 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
Post #455297
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse