SQL Server Multiple JOINS with Table Value Function - query never ends I have a query with 4 joins using a table value function to get the data and when I execute it the query never ends.

Issue Details 

Table valued function:

CREATE FUNCTION [dbo].[GetIndicator] 
(     
      @indicator varchar(50),
      @refDate datetime
)
RETURNS 
TABLE
AS
RETURN
    SELECT
        T1.Id 
        , T1.ColINT_1
        , T1.ColNVARCHAR_1 collate DATABASE_DEFAULT as ColNVARCHAR_1 
        , T1.ColNVARCHAR_2 
        , T1.ColSMALLDATETIME_1
        , T1.ColDECIMAL_1
        , T1.ColDECIMAL_1

    FROM TABLE2 T2
    JOIN TABLE3 T3
    ON T2.COLFKT3 = T3.Id
        AND T3.ReferenceDate = @RefDate
        AND T3.State != 'Deleted'
    JOIN TABLE4 T4
        ON T2.COLFKT4 = T4.Id AND T4.Name=@indicator
    JOIN TABLE1 T1
        ON T2.COLFKT1=T1.Id


Query:

DECLARE @RefDate datetime
SET @RefDate = '30 April 2014 23:59:59'

SELECT DISTINCT OTHERTABLE.Id As Id
    FROM 
            GetIndicator('ID#1_0#INDICATOR_X',@RefDate) AS OTHERTABLE
        JOIN GetIndicator('ID#1_0#INDICATOR_Y',@RefDate) AS YTABLE  
            ON OTHERTABLE.SomeId=YTABLE.SomeId
            AND OTHERTABLE.DateOfEntry=YTABLE.DateOfEntry
        JOIN GetIndicator('ID#1_0#INDICATOR_Z',@RefDate) AS ZTABLE
            ON OTHERTABLE.SomeId=ZTABLE.SomeId
            AND OTHERTABLE.DateOfEntry=ZTABLE.DateOfEntry
        JOIN GetIndicator('ID#1_0#INDICATOR_W',@RefDate) AS WTABLE 
            ON OTHERTABLE.SomeId=WTABLE.SomeId
            AND OTHERTABLE.DateOfEntry=WTABLE.DateOfEntry
        JOIN GetIndicator('ID#1_0#INDICATOR_A',@RefDate) AS ATABLE  
            ON OTHERTABLE.SomeId=ATABLE.SomeId
            AND OTHERTABLE.DateOfEntry=ATABLE.DateOfEntry


Other details:


SQL server version: 2008 R2
If I execute the table function code outside the query, with the same args, the execution time is less the 1s.
Each table function call return between 250 and 500 rows.
Try changing the function to a non-inline table value function, with a clustered index. This will store the tvf's result set in an internal temp table, which may help you. A draft of what I mean:

CREATE FUNCTION [dbo].[GetIndicator] 
(     
  @indicator varchar(50),
  @refDate datetime
)
RETURNS @out TABLE (
    Id int NOT NULL,
    ColINT_1 int NOT NULL,
    -- ... and so on
    PRIMARY KEY CLUSTERED (Id)
)
AS

BEGIN;

    INSERT INTO @out
    SELECT
    T1.Id 
    , T1.ColINT_1
    , T1.ColNVARCHAR_1 collate DATABASE_DEFAULT as ColNVARCHAR_1 
    , T1.ColNVARCHAR_2 
    , T1.ColSMALLDATETIME_1
    , T1.ColDECIMAL_1
    , T1.ColDECIMAL_1

    FROM TABLE2 T2
        JOIN TABLE3 T3
            ON T2.COLFKT3 = T3.Id
        AND T3.ReferenceDate = @RefDate
        AND T3.State != 'Deleted'
    JOIN TABLE4 T4
    ON T2.COLFKT4 = T4.Id AND T4.Name=@indicator
    JOIN TABLE1 T1
        ON T2.COLFKT1=T1.Id;

    RETURN;

END;


Essentially, I think it's a performance issue you're facing. Here's a simplified view of what happens: SQL Server "expands" the inline table value functions, pretty much like it would with a view. This way, you get 12 joins in a single query:

1. othertable
   join table2, table3, table4
   join table2, table3, table4
   join table2, table3, table4
   join table2, table3, table4


A table value function that isn't inline, however, stores its output in a form of temporary table that it returns to the caller, so it isn't expanded the same way.

1. (join table2, table3, table4) -&gt; store in #temp1
2. (join table2, table3, table4) -&gt; store in #temp2
3. (join table2, table3, table4) -&gt; store in #temp3
4. (join table2, table3, table4) -&gt; store in #temp4
5. join othertable, #temp1, #temp2, #temp3, #temp4


Here, the workload is split up into multiple elements. Some situations will benefit from an inline table-value function, whereas others (like yours) will suffer a performance penalty. It has to do with how many tables you're joining, the amount of data in each table, indexing, etc.