title: Optimize multiple joins with table functions
body: <p>I would like to join several times with the same table function for different input variables in the same query. But this turns in my case out to be much slower than using table variables and selecting from the table functions separately.</p>
  
  <p>How can I avoid table variables and still have a fast query?</p>
  
  <p>For example, we have a SQL query like</p>
  
  <pre>
  SELECT P.ProjectName, A.Number, B.Number
  FROM Project AS P
  LEFT JOIN dbo.fn_ProjectNumber(@dateA) AS A
      ON P.ProjectID = A.ProjectID
  LEFT JOIN dbo.fn_ProjectNumber(@dateB) AS B
      ON P.ProjectID = B.ProjectID
  </pre>
  
  <p>but it is much slower than selecting from the functions separately into variables and then joining later, for example:</p>
  
  <pre>
  INSERT INTO @tempA
  SELECT P.ProjectID, A.Number
  FROM Project AS P
  LEFT JOIN dbo.fn_ProjectNumber(@dateA) AS A
      ON P.ProjectID = A.ProjectID
  
  INSERT INTO @tempB
  SELECT P.ProjectID, B.Number
  FROM Project AS P
  LEFT JOIN dbo.fn_ProjectNumber(@dateB) AS B
      ON P.ProjectID = B.ProjectID
  
  SELECT P.ProjectName, A.Number, B.Number
  FROM Project AS P
  LEFT JOIN @tempA AS A
      ON P.ProjectID = A.ProjectID
  LEFT JOIN @tempA AS B
      ON P.ProjectID = B.ProjectID
  </pre>
  
  <p>What could be the cause of this? Is there a way I can get a fast query and avoid table variables?</p>
  
  <hr>
  
  <p>More details:</p>
  
  <p>This is only an example similar to what I'm doing, but the function <code>fn_ProjectNumber(@date datetime)</code> would contain something like joins between four tables...</p>
  
  <pre>
  </pre>