Performance of outer apply with function

sql-server, sql-server-2008

Solution

It depends of function type:

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.

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.

Problem

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 ```

Original source