Why is inserting into and joining #temp tables faster??

Question

I have a query that looks like

SELECT
 P.Column1,
 P.Column2,
 P.Column3,
 ...
 (
   SELECT
       A.ColumnX,
       A.ColumnY,
       ...
   FROM
      dbo.TableReturningFunc1(@StaticParam1, @StaticParam2) AS A
   WHERE
      A.Key = P.Key
   FOR XML AUTO, TYPE  
 ),
 (
   SELECT
       B.ColumnX,
       B.ColumnY,
       ...
   FROM
      dbo.TableReturningFunc2(@StaticParam1, @StaticParam2) AS B
   WHERE
      B.Key = P.Key
   FOR XML AUTO, TYPE  
 )
FROM
(
   <joined tables here>
) AS P
FOR XML AUTO,ROOT('ROOT')

P has ~ 5000 rows A and B ~ 4000 rows each

This query has a runtime performance of ~10+ minutes.

Changing it to this however:

SELECT
 P.Column1,
 P.Column2,
 P.Column3,
 ...
INTO #P

SELECT
 A.ColumnX,
 A.ColumnY,
 ...
INTO #A     
FROM
 dbo.TableReturningFunc1(@StaticParam1, @StaticParam2) AS A

SELECT
 B.ColumnX,
 B.ColumnY,
 ...
INTO #B     
FROM
 dbo.TableReturningFunc2(@StaticParam1, @StaticParam2) AS B


SELECT
 P.Column1,
 P.Column2,
 P.Column3,
 ...
 (
   SELECT
       A.ColumnX,
       A.ColumnY,
       ...
   FROM
      #A AS A
   WHERE
      A.Key = P.Key
   FOR XML AUTO, TYPE  
 ),
 (
   SELECT
       B.ColumnX,
       B.ColumnY,
       ...
   FROM
      #B AS B
   WHERE
      B.Key = P.Key
   FOR XML AUTO, TYPE  
 )
FROM #P AS P
FOR XML AUTO,ROOT('ROOT')

Has a performance of ~4 seconds.

This makes not a lot of sense, as it would seem the cost to insert into a temp table and then do the join should be higher by default. My inclination is that SQL is doing the wrong type of "join" with the subquery, but maybe I've missed it, there's no way to specify the join type to use with correlated subqueries.

Is there a way to achieve this without using #temp tables/@table variables via indexes and/or hints?

EDIT: Note that dbo.TableReturningFunc1 and dbo.TableReturningFunc2 are inline TVF's, not multi-statement, or they are "parameterized" view statements.

Answer 1

Your procedures are being reevaluated for each row in P.

What you do with the temp tables is in fact caching the resultset generated by the stored procedures, thus removing the need to reevaluate.

Inserting into a temp table is fast because it does not generate redo / rollback.

Joins are also fast, since having a stable resultset allows possibility to create a temporary index with an Eager Spool or a Worktable

You can reuse the procedures without temp tables, using CTE's, but for this to be efficient, SQL Server needs to materialize the results of CTE.

You may try to force it do this with using an ORDER BY inside a subquery:

WITH    f1 AS
        (
        SELECT  TOP 1000000000
                A.ColumnX,
                A.ColumnY
        FROM    dbo.TableReturningFunc1(@StaticParam1, @StaticParam2) AS A
        ORDER BY
                A.key
        ),
        f2 AS
        (
        SELECT  TOP 1000000000
                B.ColumnX,
                B.ColumnY,
        FROM    dbo.TableReturningFunc2(@StaticParam1, @StaticParam2) AS B  
        ORDER BY
                B.Key
        )
SELECT  …

, which may result in Eager Spool generated by the optimizer.

However, this is far from being guaranteed.

The guaranteed way is to add an OPTION (USE PLAN) to your query and wrap the correspondind CTE into the Spool clause.

See this entry in my blog on how to do that:

• Generating XML in subqueries

This is hard to maintain, since you will need to rewrite your plan each time you rewrite the query, but this works well and is quite efficient.

Using the temp tables will be much easier, though.

Answer 2

It is a problem with your sub-query referencing your outer query, meaning the sub query has to be compiled and executed for each row in the outer query. Rather than using explicit temp tables, you can use a derived table. To simplify your example:

SELECT P.Column1,
       (SELECT [your XML transformation etc] FROM A where A.ID = P.ID) AS A

If P contains 10,000 records then SELECT A.ColumnX FROM A where A.ID = P.ID will be executed 10,000 times.
You can instead use a derived table as thus:

SELECT P.Column1, A2.Column FROM  
P LEFT JOIN 
 (SELECT A.ID, [your XML transformation etc] FROM A) AS A2 
 ON P.ID = A2.ID

Okay, not that illustrative pseudo-code, but the basic idea is the same as the temp table, except that SQL Server does the whole thing in memory: It first selects all the data in "A2" and constructs a temp table in memory, then joins on it. This saves you having to select it to TEMP yourself.

Just to give you an example of the principle in another context where it may make more immediate sense. Consider employee and absence information where you want to show the number of days absence recorded for each employee.

Bad: (runs as many queryes as there are employees in the DB)

SELECT EmpName, 
 (SELECT SUM(absdays) FROM Absence where Absence.PerID = Employee.PerID) AS Abstotal        
FROM Employee

Good: (Runs only two queries)

SELECT EmpName, AbsSummary.Abstotal
FROM Employee LEFT JOIN
      (SELECT PerID, SUM(absdays) As Abstotal 
       FROM Absence GROUP BY PerID) AS AbsSummary
ON AbsSummary.PerID = Employee.PerID

Answer 3

Consider using the WITH common_table_expression construct for what you now have as sub-selects or temporary tables, see http://msdn.microsoft.com/en-us/library/ms175972(SQL.90).aspx .

Answer 4

This makes not a lot of sense, as it would seem the cost to insert into a temp table and then do the join should be higher by de> This makes not a lot of sense, as it would seem the cost to insert into a temp table and then do the join should be higher by default.fault.

With temporary tables, you explitly instruct Sql Server which intermediate storage to use. But if you stash everything in a big query, Sql Server will decide for itself. The difference is not really that big; at the end of the day, temporary storage is used, whether you specify it as a temp table or not.

In your case, temporary tables work faster, so why not stick to them?

Answer 5

There are several possible reasons why using intermediate Temp tables might speed up a query, but the most likely in your case is that the functions which are being called (but are not listed), are probably Multi-statement TVF's and not in-line TVF's. Multi-statement TVF's are opaque to the optimization of their calling queries and thus the optimizer cannot tell if there are any oppurtunities for re-use of data, or other logical/physical operator re-ordering optimizations. Thus, all it can do is to re-execute the TVFs every time that the containing query is supposed to produce another row with the XML columns.

In short, multi-statement TVF's frustrate the optimizer.

The usual solutions, in order of (typical) preference are:

1. Re-write the offending multi-statement TVF to be an in-line TVF
2. In-line the function code into the calling query, or
3. Dump the offending TVF's data into a temp table. which is what you've done...

Answer 6

If creating a temp table with intermediate results is faster, why doesn't the optimizer just do it on its own? Isn't that the job of the optimizer?

All of the other answers so far talk about how to just get to same point you're already at.

Answer 7

If temp tables turn out to be faster in your particular instance, you should instead use a table variable.

There is a good article here on the differences and performance implications:

http://www.codeproject.com/KB/database/SQP_performance.aspx