sql server - SQL 2005 CTE vs TEMP table Performance when used in joins of other tables

Question

I have a complex query that I need to use in a subsequent query (actually update statement). I have tried both using a CTE and a temp table. The performance using the CTE is horrible vs the temp table approach. Its something like 15 seconds vs milliseconds. To simplify the test instead of joining the CTE/Temp table in the subsequent query I simply selected * from it. In that case they perform the same.

I Have Looked at The Execution Plan for both approaches both with the joins in the subsequent query and then simply select *. With the simple select the query plans are about the same, but with the joins in the subsequent select the query plans are not. Specifically the portion of the query plan for creating and populating the temp table stays the same, while the query plan portion for creating and populating the CTE changes dramatically when it is subsequently used in a query with a join.

My question is why does the query plan for the creation and population of the CTE change by how it is subsequently used while the temp table is not. Also in what scenarios then would a CTE yield better performance than a temp table?

*Note I have used a table variable as well and it is comparable to the temp table approach.

Thanks

Answer 1

CTE is just an alias for the query.

It may (or may not) be rerun each time it's used.

There is no clean way to force CTE materialization in SQL Server (like Oracle's /*+ MATERIALIZE */), and you have to do dirty tricks like this:

• Generating XML in subqueries

CTE may improve performance if used in plans requiring only one evaluation (like HASH JOIN, MERGE JOIN etc.).

In these scenarios, the hash table will be built right from the CTE, while using the temp table will require evaluating the CTE, pulling the results into the temp table and reading the temp table once again.