February 12, 2008 at 8:44 pm
Comments posted to this topic are about the item User Defined Function Execution
February 13, 2008 at 12:04 am
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
February 13, 2008 at 4:04 am
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?
February 13, 2008 at 4:24 am
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
February 13, 2008 at 5:13 am
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 thatSELECT ... FROM udfwill 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
February 13, 2008 at 5:24 am
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
February 13, 2008 at 7:47 am
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
February 13, 2008 at 9:10 am
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 Varsha
February 13, 2008 at 11:07 am
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.
February 13, 2008 at 12:28 pm
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
February 13, 2008 at 12:53 pm
Hm, I am having a blackout:
Why is this function not deterministic?!?
CREATE FUNCTION dbo.fnDateAdd()
RETURNS int
AS
BEGIN
RETURN 0
END
GO
Do I need to have any special session settings enabled?
SELECT objectpropertyex(object_id('dbo.fnDateAdd'), N'IsDeterministic')
returns 0.
I am stuck... 🙁
Btw: I prefer "wrong" questions with discussions that start your brain on a topic over questions where I just get my points.
Best Regards,
Chris Büttner
February 13, 2008 at 1:05 pm
I think you have to have input parameters. If the function is deterministic it will return a consistent result given the same input parameter(s).......give that a whirl...
🙂
February 13, 2008 at 1:09 pm
Like this?
CREATE FUNCTION dbo.fnDateAdd(@intA int)
RETURNS int
AS
BEGIN
RETURN @intA
END
GO
Still no luck... 🙁
Best Regards,
Chris Büttner
February 13, 2008 at 1:18 pm
Christian Buettner (2/13/2008)
Hm, I am having a blackout:Why is this function not deterministic?!?
CREATE FUNCTION dbo.fnDateAdd()RETURNS int
AS
BEGIN
RETURN 0
END
GO
Do I need to have any special session settings enabled?
SELECT objectpropertyex(object_id('dbo.fnDateAdd'), N'IsDeterministic')
returns 0.
I am stuck... 🙁
Btw: I prefer "wrong" questions with discussions that start your brain on a topic over questions where I just get my points.
It IS deterministic, and SQL Server knows it - but somehow that setting never gets set:).
Prove it to yourself.
create function dbo.fndateadd2()
returns int
as begin
return dbo.fndateadd()
end
go
It won't let you use anything non-deterministic when creating a function, ergo QED.
Long way to get to "that ObjectProperty be broke" part....:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 13, 2008 at 1:21 pm
Chris, it appears that SQL Server requires the WITH SCHEMABINDING function option in order for a function to be identified as deterministic.
Here is a completely meaningless sample to show how SQL server handles deterministic functions.
ALTER FUNCTION dbo.fnDateAdd(@intA int)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
RETURN @intA
END
GO
-- This should return 1
SELECT objectpropertyex(object_id('dbo.fnDateAdd'), N'IsDeterministic')
-- This will only call the function once
SELECT * FROM sysobjects WHERE [id] <= dbo.fnDateAdd(object_id('dbo.fnDateAdd'))
-- This will call the function for every row in sysobjects.
SELECT * FROM sysobjects WHERE [id] <= dbo.fnDateAdd([id])
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply