Performance impact of chained CTE vs Temp table

user_1955609 0
Viewed 2116

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

sqlperformance common-table-expression tsql sql-server sql
3 Answers

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.

user_11440350

Consider cte1 is expensive 

;WITH CTE1
AS(    
    SELECT * FROM TableA
)

SELECT * INTO #Temp FROM CTE1

Above guarantees cte1 is only run once.

The chained cte can evaluate cte1 multiple times.

And even with #temp you should consider index / PK and sort the insert.

user_6073140

This depends upon many factors. Always try to write the single statement, if you can. Premature optimization is the root of a lot of evil.

If you do experience a performance problem, these are some of the advantages to decomposing your single statement:

  • It can increase maintainability, which is one of many non-functional requirements, by reducing complexity.
  • It can yield a better plan, so long as the cost of the intermediate materialization and the time saved is less than the original cost.
  • The intermediate tables can be indexed.
  • Indexes, primary keys, and unique constraints are very helpful to the optimizer, not only for choosing join types, but also for estimating cardinality, which has a large effect on memory grants.
  • You can choose to apply optimizer hints, such as MAXDOP only to select statements, rather than one gigantic statement. This is especially helpful when you need to manipulate memory grants.
  • You can tune individual statements to eliminate spill to tempdb.
  • Depending upon the complexity and total execution time of your process, you can potentially release resource locks earlier, depending also upon which isolation level your statements run under.
  • If your query plan is poor, due to an optimizer time-out, using less complex individual statements may yield better overall results.
user_102240
Related