Performance impact of chained CTE vs Temp table

Question

I have the following Chained CTE query (simplified):

;WITH CTE1
AS(    
    SELECT * FROM TableA
),
CTE2
AS(
    SELECT * FROM TableB b INNER JOIN CTE1 c ON b.id = c.id
)

SELECT * FROM CTE2

If I break CTE chain and store data of CTE1 into a temp table then the performance of the overall query improves (from 1 minute 20 seconds to 8 seconds).

;WITH CTE1
AS(    
    SELECT * FROM TableA
)

SELECT * INTO #Temp FROM CTE1

;WITH CTE2
AS(
    SELECT * FROM TableB b INNER JOIN #Temp c ON b.id = c.id
)

SELECT * FROM CTE2
DROP TABLE #Temp

There are complex queries in CTE1 and CTE2. I have just created a simplified version to explain here.

Should breaking CTE chair improve the performance?

SQL Server version: 2008 R2

Answer 1

Obviously, it can, as you yourself have shown.

Why? The most obvious reason is that the optimizer knows the size of a temporary table. That gives it more information for optimizing the query. The CTE is just an estimate. So, the improvement you are seeing is due to the query plan.

Another reason would be if the CTE is referenced multiple times in the query. SQL Server does not materialize CTEs, so the definition code would be run multiple times.

Sometimes, you purposely materialize CTEs as temporary tables so you can add indexes to them. That can also improve performance.

All that said, I prefer to avoid temporary tables. The optimizer is usually pretty good.