THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Reuse Your Code with Table-Valued UDFs

 Suppose you are using SQL Server 2005, and you want to truncate your DATETIME value, leaving only date part of it, as follows:

 

DATEADD(day, DATEDIFF(day, '19010101', LastModifiedDate), '19010101')

 

Suppose you want to reuse this code and wrap it in a scalar UDF:

 

CREATE FUNCTION dbo.TruncatedDateTime(@d DATETIME)

RETURNS DATETIME

AS

BEGIN

RETURN DATEADD(day, DATEDIFF(day, '19010101', @d), '19010101')

END

GO

 

It is well known that scalar UDFs may be slow, but how slow is this particular one, as compared to the expression which it wraps? That’s quite easy to figure out. I populated a table with one million dates from one of my production tables, as follows:

 

SELECT TOP 1000000 LastModifiedDate INTO dates

FROM data.MyProductionTable

 

 

And I ran these two queries:

 

SELECT COUNT(*) FROM(

SELECT DATEADD(day, DATEDIFF(day, '19010101', LastModifiedDate), '19010101') AS d FROM dates

) t WHERE d>'20220203'

 

(snip)

SQL Server Execution Times:

   CPU time = 343 ms,  elapsed time = 347 ms.

 

 

SELECT COUNT(*) FROM(

SELECT dbo.TruncatedDateTime(LastModifiedDate) AS d FROM Dates

) t WHERE d>'20220203'

 

(snip)

 

SQL Server Execution Times:

   CPU time = 26109 ms,  elapsed time = 26651 ms.

 

Note that the UDF slowed the query down almost one thousand times, as expected. However, let us consider a better alternative – a  table-valued UDF, as follows:

 

CREATE FUNCTION dbo.TableValuedTruncatedDateTime(@d DATETIME)

RETURNS TABLE

AS

RETURN( SELECT DATEADD(day, DATEDIFF(day, '19010101', @d), '19010101') AS TruncatedDateTime);

GO

 

SELECT COUNT(*) FROM(

SELECT TruncatedDateTime AS d FROM Dates CROSS APPLY dbo.TableValuedTruncatedDateTime(Dates.LastModifiedDate)

) t WHERE d>'20220203'

 

(snip)

SQL Server Execution Times:

   CPU time = 344 ms,  elapsed time = 347 ms.

 

 

Note that the query using the table-valued UDF runs just as fast as the original one. As you have seen, CROSS APPLY allowed you to reuse your date-truncating code without taking a performance hit.

 

My other posts discussing T-SQL code reuse and performance are as follows:

 
Will wrapping up code in a reusable stored procedure hurt performance?

 
More specific queries may run faster, but we should not overdo it.

Published Friday, May 23, 2008 1:26 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

Thanks for sharing this Alex, I have used CROSS APPLY in this way as well, but never thought to use it for date truncation specifically... makes perfect sense.

May 23, 2008 3:50 PM
 

Alejandro Mesa said:

Hi Alex,

Thanks for sharing this with us.

What makes you think that it is the use of the CROSS APPLY operator which is improving the performance, and not the use of an inline table-valued function?

What about comparing the following statement with the one using the cross apply operator.

select count(*)

from (

select

   (

   select TruncatedDateTime

   from dbo.TableValuedTruncatedDateTime(d.LastModifiedDate)

   ) as c1

from dates as d

) as t1

I got same execution plans, but always a better elapsed time for the one using the cross apply operator, but not much diffenrece.

Regards,

AMB

May 24, 2008 11:49 AM
 

Alexander Kuznetsov said:

Alejandro,

I think that the use of an inline table-valued function provides the same performance as the use of my original expression DATEADD(day, DATEDIFF(day, '19010101', LastModifiedDate), '19010101'). Also the use of the CROSS APPLY allows for shorter and neater code as compared to other alternatives. I have chosen date truncation as the simplest example. I frequently deal with more complex expressions such as "third Wednesday of the month" or "last Friday of the month" or "previous Monday", where the benefit of simpler code is more pronounced.

May 24, 2008 12:25 PM
 

Adam Machanic said:

May 25, 2008 2:07 PM
 

Eric Isaacs said:

It would be interesting to see your results if you use the WITH SCHEMABINDING option on your scalar UDF function.  All those functions that should use WITH SCHEMABINDING to improve their performance, especially when there is no schema to bind to.

August 4, 2008 9:24 PM
 

Eric Isaacs said:

I did my own tests and not using WITH SCHEMABINDING on this scalar UDF costs about another 10 to 42% over using the same function with schemabinding, with the typical additional cost of somewhere around 33% above the function that uses WITH SCHEMABINDING.  This is still not a reason to use a scalar UDF in place of the table UDF, but it is a case to use the WITH SCHEMABINDING with scalar UDFs when they are appropriate.  Here's my test script on Adventureworks.  Make sure to run it a few times equal out the effects of any caching...

USE AdventureWorks

CREATE FUNCTION dbo.TruncatedDateTimeWithSchemaBinding (@d DATETIME)

RETURNS DATETIME

   WITH SCHEMABINDING  -- <== Add this

AS

BEGIN

   RETURN DATEADD(day, DATEDIFF(day, '19010101', @d), '19010101')

END

GO

CREATE FUNCTION dbo.TruncatedDateTimeWithoutSchemaBinding (@d DATETIME)

RETURNS DATETIME

--WITH SCHEMABINDING  

AS

BEGIN

   RETURN DATEADD(day, DATEDIFF(day, '19010101', @d), '19010101')

END

--Rerun this SQL below a few times to generate test results...

DECLARE

   @StartDateTime_NO_SCHEMABINDING DATETIME,

   @EndDateTime_NO_SCHEMABINDING AS DATETIME,

   @StartDateTime_SCHEMABINDING DATETIME,

   @EndDateTime_SCHEMABINDING AS DATETIME

SET @StartDateTime_NO_SCHEMABINDING = GETDATE()

SELECT

   Purchasing.PurchaseOrderDetail.ModifiedDate,

   dbo.TruncatedDateTimeWithSchemaBinding(Purchasing.PurchaseOrderDetail.ModifiedDate)

FROM

   Purchasing.PurchaseOrderDetail

SET @EndDateTime_NO_SCHEMABINDING = GETDATE()

SET @StartDateTime_SCHEMABINDING = GETDATE()

SELECT

   Purchasing.PurchaseOrderDetail.ModifiedDate,

   dbo.TruncatedDateTimeWithoutSchemaBinding(Purchasing.PurchaseOrderDetail.ModifiedDate)

FROM

   Purchasing.PurchaseOrderDetail

SET @EndDateTime_SCHEMABINDING = GETDATE()

SELECT

   DATEDIFF(millisecond, @StartDateTime_SCHEMABINDING, @EndDateTime_SCHEMABINDING) AS SchemaBindingMS,

   DATEDIFF(millisecond, @StartDateTime_NO_SCHEMABINDING, @EndDateTime_NO_SCHEMABINDING) AS NoSchemaBindingMS,

   CAST(DATEDIFF(millisecond, @StartDateTime_SCHEMABINDING, @EndDateTime_SCHEMABINDING) AS FLOAT) / CAST(DATEDIFF(millisecond, @StartDateTime_NO_SCHEMABINDING, @EndDateTime_NO_SCHEMABINDING) AS FLOAT) AS SCHEMABINDING_over_NoSchemaBinding,

   CAST(DATEDIFF(millisecond, @StartDateTime_NO_SCHEMABINDING, @EndDateTime_NO_SCHEMABINDING) AS FLOAT) / CAST(DATEDIFF(millisecond, @StartDateTime_SCHEMABINDING, @EndDateTime_SCHEMABINDING) AS FLOAT) AS NoSchemaBinding_over_SCHEMABINDING

August 4, 2008 10:32 PM
 

Alexander Kuznetsov said:

Hi Eric,

I'm on vacation right now. I'll reply when I am back.

August 5, 2008 10:22 AM
 

Joe Blogs said:

You get 8 weeks vacation time? Please let the rest of us know if you have any job openings...  ;-p

September 30, 2008 10:14 AM
 

sam said:

Hey Joe - did you get the job?

March 18, 2009 12:27 PM
 

Alexander Kuznetsov said:

It is well known that nested calls of scalar UDFs are a huge drag on performance, but in many cases inline

June 18, 2009 10:49 PM
 

Alexander Kuznetsov said:

Problems such as calculating third Wednesday of the month or the last day of the month are very common.

June 21, 2009 10:40 PM
 

John Price said:

Chiming in to say I had a proc that was calling a scalar function for 13,000 rows (as one of several steps!) and was taking almost 3 minutes to complete.  I used the method in the article to change to a CROSS APPLY to a TVF and the performance is consistently 19 sec.  Still slow but an order of magnitude faster.  Great tip!

February 21, 2013 4:24 PM
 

Ira Gladnick said:

Is there an explanation as to why the scalar valued function is slower?

Either way, it seems that you need to evaluate the function once per row.  So why the big difference?

February 24, 2013 10:59 AM
 

Ludvig Derning said:

CROSS APPLY Really slowed down my query, the table valued inline function was an amazing performance boost though.

That said the CROSS APPLY was applied as the last step in a massive join.

I suppose it might be quicker to use it in a separate subsequent query on the previous result?

June 5, 2013 10:47 AM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement