Search results matching tags 'Transact SQL' and 'Inline UDFs'

Multi-statement TVFs are essentially slowish nested loops.

Alexander Kuznetsov — Tue, 02 Feb 2010 20:53:00 GMT

Whenever we are using multi-statement TVFs, we are essentially forcing nested loops logic on the database engine. Although multi-statement TVFs are smart enough and do not always execute once per row, when they do so, they may be much slower than nested loops.

As usual, inline UDFs shine as compared to multi-statement ones, at least in all the benchmarks in this post - let us run some tests and see for ourselves.

Setting up data

First of all, here is a table, rather large, 512K rows, and rather wide, less than 20 rows per page, so that reading from it via nested loops is a natural choice in many cases:

CREATE TABLE dbo.Parent ( ID INT NOT NULL PRIMARY KEY , ParentNumber INT NOT NULL, SpaceFiller CHAR(500) NOT NULL ) ; GO DECLARE @adder INT ; SET @adder = 1 ; INSERT INTO dbo.Parent ( ID, ParentNumber, SpaceFiller ) VALUES ( 1, 1, '#' ) ; WHILE @adder < 500000 BEGIN ; INSERT INTO dbo.Parent ( ID , ParentNumber , SpaceFiller ) SELECT ID + @adder , ParentNumber , SpaceFiller FROM dbo.Parent ; SET @adder = @adder * 2 ; END ; GO

The Child table is much smaller and narrow:

CREATE TABLE dbo.Child ( ID INT NOT NULL PRIMARY KEY , ParentID INT NOT NULL , ChildNumber INT NOT NULL UNIQUE ) ; GO INSERT INTO dbo.Child ( ID, ParentID, ChildNumber ) SELECT ID, -- ID - int ID, -- ParentID - int ID -- ChildNumber - int FROM dbo.Parent WHERE ID BETWEEN 1 AND 1000 ;

Comparing nested loops vs. multi-statement UDF calls

Here are the two UDFs which we are going to benchmark:

CREATE FUNCTION dbo.GetParentNumber_Multiline ( @ParentID INT ) RETURNS @parentInfo TABLE ( ParentNumber INT ) AS BEGIN ; INSERT @parentInfo ( ParentNumber ) SELECT ParentNumber FROM dbo.Parent WHERE ID = @ParentID ; RETURN ; END ; GO CREATE FUNCTION GetParentNumber_Inline ( @ParentID INT ) RETURNS TABLE AS RETURN ( SELECT ParentNumber FROM dbo.Parent WHERE ID = @ParentID ) ;

Let us fire up the Profiler, make sure it records individual statements, and run the following script:

SELECT ID, ParentID, p.ParentNumber INTO #t1 FROM dbo.Child CROSS APPLY dbo.GetParentNumber_Multiline ( ParentID ) AS p ; DROP TABLE #t1 ; SELECT ID, ParentID, p.ParentNumber INTO #t1 FROM dbo.Child CROSS APPLY dbo.GetParentNumber_Inline ( ParentID ) AS p DROP TABLE #t1 ;

In the Profiler, we shall see that the multi-statement UDF has been invoked 1000 times, which is slow:

Multiline stats:

CPU time = 312 ms, elapsed time = 696 ms.

Inline stats:

CPU time = 0 ms, elapsed time = 7 ms.

Clearly the difference in performance is dramatic, while the plan is essentially the same - nested loops. We can highlight the query, compare the execution plans, and see for ourselves.

Multi-statement UDFs do not always run once per row

While we are at it, let us bust a myth: multi-statement UDFs do not have to run once per row. Let us have all child rows refer to only one parent:

UPDATE dbo.Child SET ParentID = 1 ;

Let us rerun our benchmarks. In the profiler we shall see that the multi-statement UDF has been invoked only once. Execution costs are also quite comparable this time for both scripts:

CPU time = 15 ms, elapsed time = 21 ms.

As we have seen, the database engine is smart enough and may sometimes execute multi-statement UDFs once per distinct set of parameters, not once per row.

Multi-statement UDFs essentially force nested loops on the engine

Let us set up test data differently, so that the second benchmark (the one using inline UDF) executes as a hash join:

UPDATE dbo.Child SET ParentID = ID ;

TRUNCATE TABLE dbo.Parent ; GO INSERT INTO dbo.Parent ( ID, ParentNumber, SpaceFiller ) SELECT ID, ID, '*' FROM dbo.Child ;

The first benchmark still executes as a nested loop, and is much slower

Multi-statement UDF, nested loops:
CPU time = 266 ms, elapsed time = 674 ms.

Inline UDF, hash join:
CPU time = 0 ms, elapsed time = 5 ms.

Conclusion

As we have seen, if we want to force nested loops, we can use multi-statement UDFs.Also we have seen that those multi-statement UDFs can be very slow.

However, let us be cautious: when we want to benchmark real life solutions, we do not want to do it with the Profiler running, like I did in this post. Also, as Dave Ballantine has correctly pointed out, in some cases STATISTICS TIME may skew the results just as well.

Calculating third Wednesday of the month with inline UDFs

Alexander Kuznetsov — Mon, 22 Jun 2009 02:06:00 GMT

Problems such as calculating third Wednesday of the month or the last day of the month are very common. Recently Peter Larsson posted a scalar UDF that solves problems such as "third Wednesday of the month", and Uri Dimant added a comment with a solution by Steve Kass. Unfortunately, all these solutions use scalar UDFs and as such are very slow, so I decided to post some very fast inline UDFs that I find very useful.

Composing a date from year, month, and day without checking if the parameters are valid.

The following inline UDF composes a date. I deliberately intend to use it with invalid parameters, such as month equal to zero, because it dramatically simplifies my logic.

CREATE FUNCTION Readers.ComposeDate(@year INT, @month INT, @day INT) -- I deliberately do not check if the parameters are valid RETURNS TABLE AS RETURN( SELECT DATEADD(DAY, @day - 1, DATEADD(MONTH, @month - 1, DATEADD(YEAR, @year - 1901, '19010101'))) AS ComposedDate ) GO

Calculating the last days of previous and this months.

Because my ComposeDate function handles out of range parameters, I don't need to handle January and December differently. Here you go, create them and see how they work:

CREATE FUNCTION Readers.LastDayOfPreviousMonth(@year INT, @month INT) RETURNS TABLE AS RETURN( SELECT ComposedDate AS LastDayOfPreviousMonth FROM Readers.ComposeDate(@year, @month, 0) ) GO SELECT LastDayOfPreviousMonth, '20081231' FROM Readers.LastDayOfPreviousMonth(2009,1) UNION ALL SELECT LastDayOfPreviousMonth, '20090131' FROM Readers.LastDayOfPreviousMonth(2009,2) GO CREATE FUNCTION Readers.LastDayOfMonth(@year INT, @month INT) RETURNS TABLE AS RETURN( SELECT ComposedDate AS LastDayOfMonth FROM Readers.ComposeDate(@year, @month+1, 0) ) GO SELECT LastDayOfMonth FROM Readers.LastDayOfMonth(2009,11) UNION ALL SELECT LastDayOfMonth FROM Readers.LastDayOfMonth(2009,12) GO

Calculating third Wednesday and other similar problems

For simplicity (this is a blog post, not production code), this function does not determine if the returned date is actually in the required month or in another one. If you need such validation, use the approach demonstrated in the next example.

CREATE FUNCTION Readers.MthDayOfNthWeek(@year INT, @month INT, @week INT, @day INT) -- @day 1 Sun, 2 Mon etc.

-- this function works in US but might need adjustments in other countries RETURNS TABLE AS RETURN( SELECT t.ComposedDate AS MthDayOfNthWeek FROM (SELECT ComposedDate FROM Readers.ComposeDate(@year, @month, 1)) AS d CROSS APPLY Readers.ComposeDate(@year, @month, @day - DATEPART(dw,ComposedDate) + 1 + 7*(@week + CASE WHEN DATEPART(dw,ComposedDate)>@day THEN 0 ELSE -1 END) ) AS t ) GO -- third Wednesday of June 2009 SELECT MthDayOfNthWeek, '20090617' AS ExpectedDate FROM Readers.MthDayOfNthWeek(2009, 6, 3, 4) UNION ALL SELECT MthDayOfNthWeek, '20090715' AS ExpectedDate FROM Readers.MthDayOfNthWeek(2009, 7, 3, 4) UNION ALL SELECT MthDayOfNthWeek, '20090601' AS ExpectedDate FROM Readers.MthDayOfNthWeek(2009, 6, 1, 2) UNION ALL SELECT MthDayOfNthWeek, '20090605' AS ExpectedDate FROM Readers.MthDayOfNthWeek(2009, 6, 1, 6) UNION ALL SELECT MthDayOfNthWeek, '20090606' AS ExpectedDate FROM Readers.MthDayOfNthWeek(2009, 6, 1, 7) UNION ALL SELECT MthDayOfNthWeek, '20090608' AS ExpectedDate FROM Readers.MthDayOfNthWeek(2009, 6, 2, 2)

This function is not completely trivial, so I used test driven development to come up with it.

Validating dates

If you need to validate dates, use the following approach:

CREATE FUNCTION Readers.ComposeValidDate(@year INT, @month INT, @day INT) RETURNS TABLE AS RETURN( SELECT CASE WHEN YEAR(ComposedDate) = @year AND MONTH(ComposedDate) = @month THEN ComposedDate END AS ComposedDate FROM Readers.ComposeDate(@year, @month, @day) ) GO SELECT ComposedDate, '20090228' AS ExpectedResult FROM Readers.ComposeValidDate(2009, 2, 28) UNION ALL SELECT ComposedDate, NULL FROM Readers.ComposeValidDate(2009, 2, 29) UNION ALL SELECT ComposedDate, NULL FROM Readers.ComposeValidDate(2009, 2, 0) UNION ALL SELECT ComposedDate, NULL FROM Readers.ComposeValidDate(2009, 0, 12)

Only the first result will validate.

Read more about performance of inline UDFs in "Reuse Your Code with Table-Valued UDFs" and "Many nested inline UDFs are very fast"