January 14, 2009 at 6:47 pm
Hello,
Could someone please help me to answer below issues?
Case 1:
select * from t1 where dt >= DateAdd(day,-1,GetDate())
How many times function invoke happend on function DateAdd() and GetDate()?
Case 2:
Create Scalar Function MyDateAdd, very simple function, just simulate system function DateAdd()
CREATE FUNCTION MyDateAdd
(
@datePart varchar(20),
@increment int,
@expression datetime
)
RETURNS datetime
AS
BEGIN
select @r = DateAdd( Day, @increment, @expression )
RETURN @r
END
GO
Then apply function myDateAdd into select statement
select * from t1 where dt >= dbo.myDateAdd('day',-1,GetDate())
How many times invoke happend on function myDateAdd()?
In profiler, LOOKS LIKE (not exactly sure) the function DateAdd() in case 1 invoked just once. However, the function myDateAdd is invoked on every record; that is, if there are 50 records exist in table t1 and only 10 record meet dt >= dbo.myDateAdd('day',-1,GetDate()) , the function myDateAdd() is invoked 50 times.
My question is,
1. How should I know how many times function DateAdd() is invoked in case 1.
2. Why is functions call is different in case 1 and case 2? Did SQL Server optimize system function (DateAdd())?
Thanks for your reply!
January 14, 2009 at 7:39 pm
rules of thumb:
- unless you mark a user defined function as SCHEMABINDING (to signify that it is deterministic), the function will essentially be run once per line.
- the built-in (edited) functions are well known, so certain tricks apply to them that just don't apply to UDF's. For example - you will find that in this case, the compiler correctly figures out that you are comparing it to a constant value, and will thus eval the function just once.
On the other hand - if you simply modifiy the function as such:
CREATE FUNCTION MyDateAdd_SB
(
@datePart varchar(20),
@increment int,
@expression datetime
)
RETURNS datetime
WITH schemabinding
as
BEGIN
declare @r datetime
select @r = DateAdd( Day, @increment, @expression )
RETURN @r
END
GO
You should see it just eval the function just once per query, since the inputs are static AND the function is deterministic.
----------------------------------------------------------------------------------
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?
January 14, 2009 at 8:15 pm
Matt that's interesting, i didn't know about WITH SCHEMABINDING to a function...
does the results have to come from a SELECT statement in order to be deterministic?
I'd assume that an IF statement would make it non-deterministic automatically...
for example, if i wanted a IsValidDate function, it would need two conditions, IF ISDATE(someval) = 1 AND someval > '1990-01-01 00:00:00.000';
Lowell
January 15, 2009 at 8:48 am
Lowell (1/14/2009)
Matt that's interesting, i didn't know about WITH SCHEMABINDING to a function...does the results have to come from a SELECT statement in order to be deterministic?
I'd assume that an IF statement would make it non-deterministic automatically...
for example, if i wanted a IsValidDate function, it would need two conditions, IF ISDATE(someval) = 1 AND someval > '1990-01-01 00:00:00.000';
I don't profess to be a full expert on all of these aspects. I do know however that even when my UDFs meets all of the OTHER criteria for being deterministic, unless I physically flag it as SCHEMABINDING, the engine still evals them row by row. There was an exhaustive thread about it over the summer (which I have of course lost).
So - it's not like you can use this to make something that truly isn't deterministic act like it is, but more like - making sure the engine treats something that IS deterministic as deterministic.
Finally - from a human perspective, I don't see why an IF statement would by itself turn something into not being deterministic, as long as you always get the same result given the same inputs (i.e. the definition of deterministic). Of course - I haven't tested that specific set, so I can't answer for sure what SQL Server will do. If I get a chance a little later, I will see about a small test.
----------------------------------------------------------------------------------
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?
January 15, 2009 at 10:19 am
well it doesn't complaina s i expected it to;
here's the way i thought it had to be, and then the way it thought would not be allowed with an "IF" statement.
I'll test them to see if they are called once or for each row, but here's an example of my question:
[font="Courier New"]
CREATE FUNCTION IsValidDate
(
@dateToCheck DATETIME
)
RETURNS INT
WITH schemabinding
AS
BEGIN
DECLARE @R INT
SELECT @R=
CASE
WHEN ISDATE(@dateToCheck) =1
THEN
CASE
WHEN @dateToCheck >= '1990-01-01 00:00:00.000'
THEN 1
ELSE 0
END
ELSE 0
END
RETURN @R
END
GO
ALTER FUNCTION IsValidDate
(
@dateToCheck DATETIME
)
RETURNS INT
WITH schemabinding
AS
BEGIN
DECLARE @R INT
IF ISDATE(@dateToCheck) =1
BEGIN
IF @dateToCheck >= '1990-01-01 00:00:00.000'
SET @R=1
ELSE
SET @R=0
END
ELSE
SET @R=0
RETURN @R
END
GO
[/font]
Lowell
January 15, 2009 at 10:26 am
I cannot think of any statement that is valid in a function that in and of itself would make a function non-deterministic. You would probably have to call something non-deterministic to cause a problem.
You might want to try calling NEWID() in the function, that should certainly cause a problem.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 15, 2009 at 10:33 am
Oh, I'm sure I've got tunnel vision on the issue, instead of understanding....my knee jerk reaction was that an IF statement would automatically void schemabinding, but that's clearly not the case.
I'm understanding it a bit better now; maybe if an IF or a CASE statement was missing an ELSE, it might not be deterministic, but i can see now how if all the logical paths are covered, we'd be good.
Lowell
January 15, 2009 at 10:42 am
Deterministic just means that given the same inputs(parameters) a function will always return the same value.
Now technically this should make any use of data tables or current-state functions (like GetDate()) a problem, but apparently Microsoft has expanded the meaning of deterministic to make it more useful by including the current data state(data tables, etc.) and system state(current time, etc.) as part of the formal "inputs" of the function.
This is legit because SQL Server forces these to always return the same values within a single statement. Thus the compiler/optimizer knows that whether it calls it once or a 100 times, same input parameters = same output result. There are some exceptions to this, however, and NEWID() from inside the function should be one.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply