How can a stored procedure calling a TVF be slower than a procedure that inlines the TVF?

Question

Question:

I have two views:

V_Room, 14969 rows, 9 tables joined together
V_parkings, 3265 rows, 9 tables joined together

Then I have a view

V_Rooms_UsageTypes with 18234 rows

which is the union between V_Room and V_parkings

Then I have a table valued function like this Function

CREATE FUNCTION [dbo].[tfu_RPT_UsageTypesBySIADetail]
(@in_reporting_date datetime)
-- Add the parameters for the function here
RETURNS TABLE

Which joins 9 tables and makes 77 subselects like below onto view V_Rooms_UsageTypes

(
        SELECT 
            ISNULL(SUM(ZO_RMArea_Area), 0.0) 
        FROM dbo.V_Rooms_UsageTypes 
        WHERE V_Rooms_UsageTypes.FL_UID = T_Floor.FL_UID 

        AND (V_Rooms_UsageTypes.DIN277_Major = 9) 
        AND (V_Rooms_UsageTypes.DIN277_Minor = 4) 

        AND (V_Rooms_UsageTypes.ZO_RMUT_DateFrom <= @in_reporting_date) 
        AND (V_Rooms_UsageTypes.ZO_RMUT_DatumTo >= @in_reporting_date) 

        AND (V_Rooms_UsageTypes.ZO_RMArea_DateFrom <= @in_reporting_date) 
        AND (V_Rooms_UsageTypes.ZO_RMArea_DatumTo >= @in_reporting_date) 

        AND (V_Rooms_UsageTypes.RM_DateFrom <= @in_reporting_date) 
        AND (V_Rooms_UsageTypes.RM_DatumTo >= @in_reporting_date) 

        AND (V_Rooms_UsageTypes.SO_DateFrom <= @in_reporting_date) 
        AND (V_Rooms_UsageTypes.SO_DatumTo >= @in_reporting_date) 

        AND (V_Rooms_UsageTypes.BG_DateFrom <= @in_reporting_date) 
        AND (V_Rooms_UsageTypes.BG_DatumTo >= @in_reporting_date) 

        AND (V_Rooms_UsageTypes.FL_DateFrom <= @in_reporting_date) 
        AND (V_Rooms_UsageTypes.FL_DatumTo >= @in_reporting_date) 
    ) AS RPT_VF_9_4 

Then I have a stored procedure which calls the table valued function using dynamic SQL (because column aliasing for multiple languages for reporting service, which needs only one consistent column name)

like this:

CREATE PROCEDURE [dbo].[sp_RPT_DATA_AreaByDIN277_old]
    @in_customer varchar(3),
    @in_language varchar(2),
    @in_site varchar(36),
    @in_building varchar(36),
    @in_floor varchar(36),
    @in_reporting_date varchar(50)
AS
    DECLARE
    @sql varchar(8000),
    @reporting_date datetime

    -- Abrunden des Eingabedatums auf 00:00:00 Uhr
    SET @reporting_date= CONVERT( DATETIME, @in_reporting_date ) 
    SET @reporting_date= Cast(Floor(Cast(@reporting_date As Float)) As DateTime)
    SET @in_reporting_date= CONVERT(varchar(50), @reporting_date ) 

    SET NOCOUNT ON;



    SET @sql='SELECT  

     FIELD_1_' + @in_language +' AS RPT_FIELD_1
    ,FIELD_2_' + @in_language +' AS RPT_FIELD_2
    ,FIELD_3 AS RPT_FIELD_3 
    ,table_valued_function_column1 AS RPT_table_valued_function_column1
    ,table_valued_function_column2 AS RPT_table_valued_function_column2
    ,table_valued_function_columnN AS RPT_table_valued_function_columnN 

    '



    SET @sql=@sql + 'FROM dbo.tfu_RPT_FM_NutzungsartenNachSIADetail(''' + @in_reporting_date + ''') '

    SET @sql=@sql + 'WHERE ST_Customer = ''' + @in_customer + ''' '


    IF @in_site     <> '00000000-0000-0000-0000-000000000000' SET @sql=@sql + 'AND (ST_UID = ''' + @in_site + ''') '
    IF @in_building <> '00000000-0000-0000-0000-000000000000' SET @sql=@sql + 'AND (BG_UID = ''' + @in_building + ''') '
    IF @in_floor    <> '00000000-0000-0000-0000-000000000000' SET @sql=@sql + 'AND (FL_UID = ''' + @in_floor + ''') '   

    EXECUTE (@sql) 

Now, expectedly, this took a rather long time (20s) for the query to return the results. So I moved the entire table valued function into the dynamic sql stored procedure and excepted to have an execution speed of roughly half (10s).

Instead, I got an execution time of 30s. Why is this ?

Answer 1

It's quite possible that the engine can better deal with optimizing the TVF separately than when it's just part of a much larger query. Also the dynamic SQL may change the way the plans are cached. You forgot to mention which version of SQL Server you're using, but have you played with the "optimize for ad hoc workloads" setting, or OPTIMIZE FOR UNKNOWN??