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 Thursday, February 14, 2008 8:54 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
I always reckon it's a good QotD if I learn something either from it or from the resulting discussion. In this case, I need to go off and look at functions defined WITH SCHEMABINDING.

Hence it was a good question to raise even if the answer was not so clear ;).

Thanks for putting it up.

Derek.


Derek
Post #455771
Posted Thursday, February 14, 2008 9:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:24 PM
Points: 7,121, Visits: 15,024
Matt Marston (2/13/2008)

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



True - but that's because one of them is taking in a scalar value as input (as in -
object_id('dbo.fnDateAdd') is going to return the same value no matter how many times you run it), and one of them is taking in a parameter that changes for each row. It really has nothing to do with the determinism of fnDateAdd.

In general - if the optimizer detects that the inputs to a scalar function are static, then the function is evaluated once, and used as a scalar value. Look at rand() or getdate() when applied to a set. The only thing I can think of that doesn't follow that pattern is NEWID() (and I suppose its companion NEWSEQUENTIALID()).


----------------------------------------------------------------------------------
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?
Post #455779
Posted Thursday, February 14, 2008 10:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:59 AM
Points: 1,917, Visits: 394
Matt Miller (2/14/2008)
Matt Marston (2/13/2008)

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



True - but that's because one of them is taking in a scalar value as input (as in -
object_id('dbo.fnDateAdd') is going to return the same value no matter how many times you run it), and one of them is taking in a parameter that changes for each row. It really has nothing to do with the determinism of fnDateAdd.


If something is going to return the same value no matter how many times you run it, then it is, by definition deterministic. So it does depend on the determinism of the function. As I stated in my original post, the number of times a function gets evaluated depends on 1) whether it gets called with the same values or with changing values and 2) whether it deterministic.

However, I do stand corrected, that in practice, it appears that SQL Server (at least when I test it on SQL Server 2005) also does further optimizatition depending on which clause the function is used in. In the WHERE clause (as shown in my previous example) it only depends on condition 1 (do the parameter values change?) and not on condition 2 (is it deterministic?). But the following example shows that elsewhere (at least in the SELECT clause) these 2 conditions must hold true. I tested on SQL Server 2005 and verified using SQL Profiler watching the SP:StmtStarting event.

CREATE FUNCTION dbo.fnDateAdd(@intA int)
RETURNS int
--WITH SCHEMABINDING
AS
BEGIN
RETURN @intA
END
GO

-- This should return 0
SELECT objectpropertyex(object_id('dbo.fnDateAdd'), N'IsDeterministic')

-- This will call the function multiple even though the parameter
-- values do not change since the function is not deterministic
SELECT *, dbo.fnDateAdd(object_id('dbo.fnDateAdd')) FROM sysobjects

GO

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 call the function once since the parameter
-- values do not change and the function is deterministic
SELECT *, dbo.fnDateAdd(object_id('dbo.fnDateAdd')) FROM sysobjects

Post #455835
Posted Thursday, February 14, 2008 11:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:24 PM
Points: 7,121, Visits: 15,024
Matt Marston (2/14/2008)

However, I do stand corrected, that in practice, it appears that SQL Server (at least when I test it on SQL Server 2005) also does further optimizatition depending on which clause the function is used in. In the WHERE clause (as shown in my previous example) it only depends on condition 1 (do the parameter values change?) and not on condition 2 (is it deterministic?). But the following example shows that elsewhere (at least in the SELECT clause) these 2 conditions must hold true. I tested on SQL Server 2005 and verified using SQL Profiler watching the SP:StmtStarting event.


Except that rule doesn't work in this case:

select getdate() from sys.all_columns

We all know that getdate() is non-deterministic. But still - you will find that you get a repeating SINGLE VALUE down the line (run that with distinct if you want to see what I mean. Perhaps the rule needs to be restricted to user-defined only.


----------------------------------------------------------------------------------
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?
Post #455894
Posted Thursday, February 14, 2008 11:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:59 AM
Points: 1,917, Visits: 394
Matt Miller (2/14/2008)

Except that rule doesn't work in this case:

select getdate() from sys.all_columns

We all know that getdate() is non-deterministic. But still - you will find that you get a repeating SINGLE VALUE down the line (run that with distinct if you want to see what I mean. Perhaps the rule needs to be restricted to user-defined only.


Yes, the "rule" is regarding user-defined functions. And it is really more of a generalization than a rule. It is basically trying to predict optimizations that SQL Server will do. GetDate() is a built-in system function that behaves differently than user defined functions. The topic of converstion here is UDFs. Try that with an equivalent UDF.

CREATE FUNCTION dbo.fnGetDate()
RETURNS datetime
AS
BEGIN
RETURN GetDate();
END

GO

select dbo.fnGetDate() as dte
into #temp
from sys.all_columns

-- notice distinct values, demonstrating that the function is called multiple times
select distinct dte from #temp

Regarding my previous example, did you run profiler with the SP:StmtStarting event selected? That is the only event necessary to see the behavior. When I run the select statement with the "non-deterministic" version (I put that in quotes because it really is determinstic but the lack of SCHEMABINDING leads SQL Server to identify it as non-deterministic) I see an SP:StmtStarting event for every row with TextData "RETURN @intA". When using the determinstic version (WITH SCHEMABINDING) the SP:StmtStarting with TextData "RETURN @intA" is only fired once (it helps to clear the profiler output in between).
Post #455898
Posted Thursday, February 14, 2008 11:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:24 PM
Points: 7,121, Visits: 15,024

Regarding my previous example, did you run profiler with the SP:StmtStarting event selected? That is the only event necessary to see the behavior. When I run the select statement with the "non-deterministic" version (I put that in quotes because it really is determinstic but the lack of SCHEMABINDING leads SQL Server to identify it as non-deterministic) I see an SP:StmtStarting event for every row with TextData "RETURN @intA". When using the determinstic version (WITH SCHEMABINDING) the SP:StmtStarting with TextData "RETURN @intA" is only fired once (it helps to clear the profiler output in between).


I did. It was a good point. Of course - I needed to run it on something bigger to see it (the differences were so small I was chalking that up to sampling errors).


----------------------------------------------------------------------------------
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?
Post #455902
Posted Monday, February 18, 2008 2:18 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
What if your UDF is a scalar detereministic UDF with a constant value passed in? How many times is this UDF executed:

CREATE FUNCTION dbo.AbsoluteValue (@i INT)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
RETURN ABS(@i);
END;
GO

SELECT dbo.AbsoluteValue(-10)
FROM sys.columns;
GO

If SQL Server is executing a deterministic UDF with a constant parameter hundreds or thousands of times, it is seriously wasting resources.
Post #457075
Posted Monday, February 18, 2008 2:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:24 PM
Points: 7,121, Visits: 15,024
Mike C (2/18/2008)
What if your UDF is a scalar detereministic UDF with a constant value passed in? How many times is this UDF executed:

CREATE FUNCTION dbo.AbsoluteValue (@i INT)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
RETURN ABS(@i);
END;
GO

SELECT dbo.AbsoluteValue(-10)
FROM sys.columns;
GO

If SQL Server is executing a deterministic UDF with a constant parameter hundreds or thousands of times, it is seriously wasting resources.


From what it looks like (based on the logical reads you see in Profiler), if you go to the trouble of marking it as deterministic (by using the WITH SCHEMABINDING), it does seem to treat it like a constant (meaning - evaluate once and pollinate once per row). But now you get to enter the world of confusing behavior: if you DON'T mark it using WITH SCHEMABINDING, the execution optimizer seems to not know that it is deterministic, and will re-evaluate it once per row. However - it DOES know that it's deterministic if you decide to call that from within another function (in 2000, since that was where it cared about deterministic components in functions).


----------------------------------------------------------------------------------
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?
Post #457084
Posted Monday, February 18, 2008 2:46 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
Matt Miller (2/18/2008)

From what it looks like (based on the logical reads you see in Profiler), if you go to the trouble of marking it as deterministic (by using the WITH SCHEMABINDING), it does seem to treat it like a constant (meaning - evaluate once and pollinate once per row). But now you get to enter the world of confusing behavior: if you DON'T mark it using WITH SCHEMABINDING, the execution optimizer seems to not know that it is deterministic, and will re-evaluate it once per row. However - it DOES know that it's deterministic if you decide to call that from within another function (in 2000, since that was where it cared about deterministic components in functions).


That's exactly my point - the question is too simplistic and not specific enough. It's asking for a simple answer to a question that's far more complex than the question would have us believe. Type of UDF and determinism factor into the question significantly. While it's probably not all that common to invoke a UDF using the Function(Constant) form, it's not uncommon to see a UDF being invoked using the Function(@Variable) form. The question seems to imply that Function(Column_Name) is the only form available and that the UDF in question is nondeterministic, which are both bad assumptions IMHO.

BTW, my friend that I blogged about (who's currently interviewing for jobs) was asked this very question a couple of days ago. I can only assume the interviewer got it from here
Post #457086
Posted Monday, February 18, 2008 7:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:24 PM
Points: 7,121, Visits: 15,024
Mike C (2/18/2008)

BTW, my friend that I blogged about (who's currently interviewing for jobs) was asked this very question a couple of days ago. I can only assume the interviewer got it from here


Brandie (who put the question in to Steve) mentioned she got it out of one of the MS Press training books...God only know where they might have picked it up.


----------------------------------------------------------------------------------
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?
Post #457161
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse