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

Many nested inline UDFs are very fast

It is well known that nested calls of scalar UDFs are a huge drag on performance, but in many cases inline UDFs can be nested without performance penalty. Here is a simple repro that you can run and see for yourself. Suppose that you already have two inline UDFs: one returning the previous calendar day and another returning the first day of the month, as follows:

 

CREATE FUNCTION Readers.GetFirstDayOfMonth(@d DATETIME)
RETURNS TABLE AS RETURN(
  
SELECT DATEADD(MONTHDATEDIFF(MONTH'19900101'@d), '19900101'AS FirstDayOfMonth
)
GO
DROP FUNCTION Readers.GetPreviousDay
CREATE FUNCTION Readers.GetPreviousDay(@d DATETIME)
RETURNS TABLE AS RETURN (
  
SELECT DATEADD(DAYDATEDIFF(DAY'19900101'@d) - 1'19900101'AS PreviousDay
)
GO
  

 

Suppose that you need to develop a UDF returning the last day of the previous month. You can develop it from scratch:

 

CREATE FUNCTION Readers.GetLastDayOfPreviousMonth_Merged(@d DATETIME)
RETURNS TABLE AS RETURN(
  
SELECT DATEADD(DAYDATEDIFF(DAY'19900101'DATEADD(MONTHDATEDIFF(MONTH'19900101'@d), '19900101')) - 1'19900101')
    
AS LastDayOfPreviousMonth

 

Alternatively you can reuse your two existing UDFs in several ways, as follows:

 

CREATE FUNCTION Readers.GetLastDayOfPreviousMonth_Nested1(@d DATETIME)
RETURNS TABLE AS RETURN(
  
SELECT p.PreviousDay AS LastDayOfPreviousMonth
    
FROM Readers.GetFirstDayOfMonth(@dAS f
    
CROSS APPLY Readers.GetPreviousDay(f.FirstDayOfMonthAS p   
)
GO
CREATE FUNCTION Readers.GetLastDayOfPreviousMonth_Nested2(@d DATETIME)
RETURNS TABLE AS RETURN(
  
SELECT p.PreviousDay AS LastDayOfPreviousMonth
    
FROM (SELECT FirstDayOfMonth FROM Readers.GetFirstDayOfMonth(@dAS fAS f
    
CROSS APPLY Readers.GetPreviousDay(f.FirstDayOfMonthAS p   
)
  

 

To benchmark, I used my Data.Numbers table whihc has 1 million rows. You can use any large enough table.

 

SET STATISTICS TIME ON
SET STATISTICS 
IO ON
GO

-- this table has 1 million rows
SELECT COUNT(*) FROM Data.Numbers

DECLARE @d DATETIME@d1 DATETIME;
SET @d '20090512';
SELECT @d1=LastDayOfPreviousMonth --INTO #t1
FROM Data.Numbers
  
CROSS APPLY Readers.GetLastDayOfPreviousMonth_Merged(@d);

GO 

DECLARE @d DATETIME@d1 DATETIME;
SET @d '20090512';
SELECT @d1=LastDayOfPreviousMonth --INTO #t1
FROM Data.Numbers
  
CROSS APPLY Readers.GetLastDayOfPreviousMonth_Nested1(@d);

GO

DECLARE @d DATETIME@d1 DATETIME;
SET @d '20090512';
SELECT @d1=LastDayOfPreviousMonth --INTO #t1
FROM Data.Numbers
  
CROSS APPLY Readers.GetLastDayOfPreviousMonth_Nested2(@d);

GO

-- this measures the overhead of benchmarking, 
-- scanning the table and assigning a value to a variable
DECLARE @d DATETIME@d1 DATETIME;
SET @d '20090512';
SELECT @d1=@d --INTO #t1
FROM Data.Numbers

 

 

I did not notice any difference in performance beyond usual noise. Of course, in some very complex cases performance of nested inline UDFs might suffer, but in general this is not the case and usually you can reuse your inline UDFs without performance penalty.

 My previous post about inline UDFs is this: Reuse Your Code with Table-Valued UDFs

Published Thursday, June 18, 2009 10:31 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

 

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

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 at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

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