Optimize multiple joins with table functions

Question

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.

How can I avoid table variables and still have a fast query?

For example, we have a SQL query like

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

but it is much slower than selecting from the functions separately into variables and then joining later, for example:

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

What could be the cause of this? Is there a way I can get a fast query and avoid table variables?

---

More details:

This is only an example similar to what I'm doing, but the function fn_ProjectNumber(@date datetime) would contain something like joins between four tables...

Answer 1

Maybe the joins are slower because you haven't defined relations between the tables that are joined together? I don't know much about performance of queries in SQL Server, but defining the relations will improve the performance of joins.

Answer 2

Try fixing the join, you refer to the wrong alias in the second LEFT JOIN:

ORIGINAL:

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 = A.ProjectID

FIXED:

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

Answer 3

Is there any particular reason you're trying to avoid table variables? They can be a good optimisation technique and don't leave any temp objects to clean up.

Anyway, if you don't want to do it that way you could always try

SELECT
    P.ProjectID, A.Number, B.Number
FROM
    Project AS P
     LEFT JOIN
      (SELECT P.ProjectID, A.Number
      FROM Project AS P
      LEFT JOIN dbo.fn_ProjectNumber(@dateA) AS A
         ON P.ProjectID = A.ProjectID
      ) AS A
          ON P.ProjectID = A.ProjectID
     LEFT JOIN
      (SELECT P.ProjectID, B.Number
      FROM Project AS P
      LEFT JOIN dbo.fn_ProjectNumber(@dateB) AS B
          ON P.ProjectID = B.ProjectID
      ) AS B
          ON P.ProjectID = B.ProjectID

Answer 4

The joins are slow because, in your example query, you are calling each function once for each row in table Project. It's faster with the temp tables because you're only calling the function once.

One way to avoid temp tables would be to use CTEs (common table expressions--they're not just for recursion--available in SQL 2005 and up.). The general syntax would be something like:

WITH cteTempName (<list of columns>)
 as (<your table function call>)
 SELECT <your query here, with "cteTempName" appearing as just another table to select from>