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

I tried creating CTE with simple selected with filter from big table Then 3 times subqueried it.

After that do the same with temporary tables.

The result was 70% time consuming for CTE -30% time consuming for temp table. So temp table is better for that solutions.

I don't think CTE makes a temp table only with selected query, but 3 times make select to a big table.

Answer 2

You're asking a complicated question, so you're getting a complicated answer: it depends. (I hate that response).

Seriously, however, it has to do with how the optimizer chooses a data plan (which you knew already); a temp table or variable is like a permanent structure in that an execution plan will perform the operation associated with filling that structure first, and then use that structure in subsequent operations. A CTE is NOT a temp table; use of the CTE is not calculated until it is being used by subsequent operations, and so that usage impacts how the plan is optimized.

CTE's were implemented for reusability and maintenance issues, not necessarily performance; however, in many cases (like recursion), they will perform better than traditional coding methods.