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.

Answer 2

Outer Apply should not be considered here...

Reason

It will Iterate for each record of the MyTable and will search corresponding record in the Outer Apply table, despite of the fact that you will get all records from MyTable. So it should be instead replaced with Join(Left/Inner). This will speed up the query especially when you have large number of records to be fetched.

Check the difference between Apply and Join