October 12, 2012 at 7:22 am
My group is having a discussion as to performance hits when a UDF is used as a macro -
IE it accesses no tables, is passed no rows. Some simple logic and a calculation on a date/time
value.
Here is an abbreviated version of the code (removed check for DST):
Create FUNCTION fnAdjustDateTime (@date datetime, @timezone int)
RETURNS datetime
AS
BEGIN
--
-- declare variables
--
declare @Dst bit
declare @offset int
declare @result datetime
--
-- Get the timezone data we will need
--
if @timezone = 1
Select @offset = -5, @Dst = 1
else if @timezone = 2
Select @offset = -6, @Dst = 1
else if @timezone = 3
Select @offset = -7, @Dst = 1
else if @timezone = 4
Select @offset = -8, @Dst = 1
else if @timezone = 5
Select @offset = -10, @Dst = 0
else if @timezone = 6
Select @offset = -9, @Dst = 1
else if @timezone = 7
Select @offset = -4, @Dst = 0
else if @timezone = 8
Select @offset = -7, @Dst = 0
else if @timezone = 9
Select @offset = -11, @Dst = 0
else
Select @offset = -6, @Dst = 1
--
-- Set result now
--
set @result = DateAdd(hh,@offset,@date)
return @result
Could someone please provide some insight on this as well a a logical explanation ??
Thanks ...
Todd
October 12, 2012 at 7:37 am
well, ideally, your scalar UDF can be converted to an inline table funciton instead.
Create FUNCTION fnAdjustDateTime_itv (@date datetime, @timezone int)
RETURNS table
AS
RETURN
SELECT DateAdd(hh,CASE
WHEN @timezone = 1 THEN -5
WHEN @timezone = 2 THEN -6
WHEN @timezone = 3 THEN -7
WHEN @timezone = 4 THEN -8
WHEN @timezone = 5 THEN -10
WHEN @timezone = 6 THEN -9
WHEN @timezone = 7 THEN -4
WHEN @timezone = 8 THEN -7
WHEN @timezone = 9 THEN -11
END,
@date) As ResultDate
GO
Create FUNCTION fnAdjustDateTime (@date datetime, @timezone int)
RETURNS datetime
AS
BEGIN
--
-- declare variables
--
declare @Dst bit
declare @offset int
declare @result datetime
--
-- Get the timezone data we will need
--
if @timezone = 1
Select @offset = -5, @Dst = 1
else if @timezone = 2
Select @offset = -6, @Dst = 1
else if @timezone = 3
Select @offset = -7, @Dst = 1
else if @timezone = 4
Select @offset = -8, @Dst = 1
else if @timezone = 5
Select @offset = -10, @Dst = 0
else if @timezone = 6
Select @offset = -9, @Dst = 1
else if @timezone = 7
Select @offset = -4, @Dst = 0
else if @timezone = 8
Select @offset = -7, @Dst = 0
else if @timezone = 9
Select @offset = -11, @Dst = 0
else
Select @offset = -6, @Dst = 1
--
-- Set result now
--
set @result = DateAdd(hh,@offset,@date)
return @result
END
GO
then the calling code gets changed to this:
select name, dbo.fnAdjustDateTime(create_date,1) from sys.objects
select name, fn.*
from sys.objects
CROSS APPLY dbo.fnAdjustDateTime_itv(create_date,1) fn
if you include actual execution palns and compare them, you will start seeing hwo the ITVF version is better; scalar functions usualyl get called once per row,
where an ITVF is treated like a set based operation
on this small dataset of using sys.objects, my results were the same; but on bigger datasets, the ITVF will be much more efficient.
Lowell
October 12, 2012 at 8:25 am
Todd Young-359443 (10/12/2012)
My group is having a discussion as to performance hits when a UDF is used as a macro -IE it accesses no tables, is passed no rows. Some simple logic and a calculation on a date/time
value.
To add to what Lowell has posted, "One test is worth a thousand expert opinions". Please see the following article for what a difference an "iSF" can make over such a Scalar UDF.
http://www.sqlservercentral.com/articles/T-SQL/91724/
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2012 at 11:46 am
I was able to work at home this weekend and found the answer. When calling
at UDF that does not access any database table and is used more like a macro,
the performance hit is negligible.
The raw difference in a 20,000 row dataset was 200 milliseconds. When I removed
the overhead of the three DateAdd functions it dropped to 100 milliseconds.
We are keeping the function in production and have dropped this as a candidate
for optimization.
October 15, 2012 at 12:09 pm
Todd Young-359443 (10/15/2012)
I was able to work at home this weekend and found the answer. When callingat UDF that does not access any database table and is used more like a macro,
the performance hit is negligible.
The raw difference in a 20,000 row dataset was 200 milliseconds. When I removed
the overhead of the three DateAdd functions it dropped to 100 milliseconds.
We are keeping the function in production and have dropped this as a candidate
for optimization.
I guess it really depends on what you call "negligible". You're talking about a function that runs twice as fast and that's not "negligible" especially when you consider that you're only working with 20,000 rows.
"Mind the pennies and the dollars will take care of themselves." 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2012 at 7:24 am
Todd Young-359443 (10/15/2012)
I was able to work at home this weekend and found the answer. When callingat UDF that does not access any database table and is used more like a macro,
the performance hit is negligible.
The raw difference in a 20,000 row dataset was 200 milliseconds. When I removed
the overhead of the three DateAdd functions it dropped to 100 milliseconds.
We are keeping the function in production and have dropped this as a candidate
for optimization.
You are fooling yourself. There are MANY reasons why UDFs are bad (see my chapter in the SQL Server MVP Deep Dives 2 book titled Death By UDF and my SQL Rally 2012 session of the same name). Depending on how this UDF gets used it could prevent the optimizer from getting accurate statistics on the query and thus lead to a disastrously bad query plan. And did you know that UDFs also void the use of parallelism? Lots of other potential issues come with them.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply