Multi-statement TVFs are essentially slowish nested loops.

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.

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.