Performance of outer apply with function

Question

I have several stored procedures that use an outer apply. The query inside the outer apply is always the same, so I could build a common table valued function which gives me the obvious benefit of code re-use, but I'm wondering if there are performance implications either way. Is there a hit I take if I call a function?

For example:

SELECT
    m.[ID],
    m.[MyField],
    o.[OtherField]
FROM
    [MyTable] m
OUTER Apply
(
    fMyFunction(m.[ID])
)

VS

SELECT
    mt.[ID],
    mt.[MyField],
    o.[OtherField]
FROM
    [MyTable] mt
OUTER Apply
(
    SELECT TOP 1
        ot.[OtherField]
    FROM
        [OtherTable] ot 
    WHERE
        ot.[ID] = m.[ID]
) o

Answer 1

It depends of function type:

1. If the function is an inline table-valued function then this function will be considered to be a "parameterized" view and SQL Server can do some optimization work.

2. If the function is multi-step table-valued function then is hard for SQL Server to optimize the statement and the output from SET STATISTICS IO will be misleading.

For the next test I used the AdventureWorks2008 (you can download this database from CodePlex). In this sample database you may find an inline table-valued function named [Sales].[ufnGetCheapestProduct]:

ALTER FUNCTION [Sales].[ufnGetCheapestProduct](@ProductID INT)
RETURNS TABLE
AS
RETURN
    SELECT   dt.ProductID
            ,dt.UnitPrice
    FROM
    (
        SELECT   d.SalesOrderDetailID
                ,d.UnitPrice
                ,d.ProductID  
                ,ROW_NUMBER() OVER(PARTITION BY d.ProductID ORDER BY d.UnitPrice ASC, d.SalesOrderDetailID) RowNumber
        FROM    Sales.SalesOrderDetail d
        WHERE   d.ProductID = @ProductID
    ) dt
    WHERE   dt.RowNumber = 1

I created a new function named [Sales].[ufnGetCheapestProductMultiStep]. This function is a multi-step table-valued function:

CREATE FUNCTION [Sales].[ufnGetCheapestProductMultiStep](@ProductID INT)
RETURNS @Results TABLE (ProductID INT PRIMARY KEY, UnitPrice MONEY NOT NULL)
AS
BEGIN
    INSERT  @Results(ProductID, UnitPrice)
    SELECT   dt.ProductID
            ,dt.UnitPrice
    FROM
    (
        SELECT   d.SalesOrderDetailID
                ,d.UnitPrice
                ,d.ProductID  
                ,ROW_NUMBER() OVER(PARTITION BY d.ProductID ORDER BY d.UnitPrice ASC, d.SalesOrderDetailID) RowNumber
        FROM    Sales.SalesOrderDetail d
        WHERE   d.ProductID = @ProductID
    ) dt
    WHERE   dt.RowNumber = 1;

    RETURN;
END

Now, we can run the next tests:

--Test 1
SELECT  p.ProductID, p.Name, oa1.*
FROM    Production.Product p
OUTER APPLY 
(
    SELECT   dt.ProductID
            ,dt.UnitPrice
    FROM
    (
        SELECT   d.SalesOrderDetailID
                ,d.UnitPrice
                ,d.ProductID  
                ,ROW_NUMBER() OVER(PARTITION BY d.ProductID ORDER BY d.UnitPrice ASC, d.SalesOrderDetailID) RowNumber
        FROM    Sales.SalesOrderDetail d
        WHERE   d.ProductID = p.ProductID
    ) dt
    WHERE   dt.RowNumber = 1
) oa1

--Test 2
SELECT  p.ProductID, p.Name, oa2.*
FROM    Production.Product p
OUTER APPLY [Sales].[ufnGetCheapestProduct](p.ProductID) oa2

--Test 3
SELECT  p.ProductID, p.Name, oa3.*
FROM    Production.Product p
OUTER APPLY [Sales].[ufnGetCheapestProductMultiStep](p.ProductID) oa3

And this is the output from SQL Profiler:

Conclusion: you can see that using a query or an inline table-valued function with OUTER APPLY will give you the same performance (logical reads). Plus: the multi-step table-valued functions are (usually) more expensive.

Note: I do not recommend using SET STATISTICS IO to measure the IO for scalar and multi-step table valued functions because the results can be wrong. For example, for these tests the output from SET STATISTICS IO ON will be:

--Test 1
Table 'SalesOrderDetail'. Scan count 504, logical reads 1513, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Test 2
Table 'SalesOrderDetail'. Scan count 504, logical reads 1513, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Test 3
Table '#064EAD61'. Scan count 504, logical reads 1008 /*WRONG*/, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.