SQL 2005 Cte VS Temp Table Performance When Used in Joins of Other Tables

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

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.

Performance impact of chained CTE vs Temp table

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.

When to use cte and temp table?

With a temp table you can use CONSTRAINT's and INDEX's. You can also create a CURSOR on a temp table where a CTE terminates after the end of the query(emphasizing a single query).

I will answer through specific use cases with an application I've had experience with in order to aid with my point.

Common use cases in an example enterprise application I've used is as follows:

---

Temp Tables

Normally, we use temp tables in order to transform data before INSERT or UPDATE in the appropriate tables in time that require more than one query. Gather similar data from multiple tables in order to manipulate and process the data.

There are different types of orders (order_type1, order_type2, order_type3) all of which are on different TABLE's but have similar COLUMN's. We have a STORED PROCEDURE that UNION's all these tables into one #orders temp table and UPDATE's a persons suggested orders depending on existing orders.

CTE's

CTE's are awesome for readability when dealing with single queries. When creating reports that requires analysis using PIVOT's,Aggregates, etc. with tons of lines of code, CTE's provide readability by being able to separate a huge query into logical sections.

---

Sometimes there is a combination of both. When more than one query is required. Its still useful to break down some of those queries with CTE's.

I hope this is of some usefulness, cheers!

Why is this CTE so much slower than using temp tables?

As you can see in the query plan, with CTEs, the engine reserves the right to apply them basically as a lookup, even when you want a join.

If it isn't sure enough it can run the whole thing independently, in advance, essentially generating a temp table... let's just run it once for each row.

This is perfect for the recursion queries they can do like magic.

But you're seeing - in the nested Nested Loops - where it can go terribly wrong.

You're already finding the answer on your own by trying the real temp table.

Why CTE (Common Table Expressions) in some cases slow down queries comparing to temporary tables in SQL Server

The answer is simple.

SQL Server doesn't materialise CTEs. It inlines them, as you can see from the execution plans.

Other DBMS may implement it differently, a well-known example is Postgres, which does materialise CTEs (it essentially creates temporary tables for CTEs behind the hood).

Whether explicit materialisation of intermediary results in explicit temporary tables is faster, depends on the query.

In complex queries the overhead of writing and reading intermediary data into temporary tables can be offset by more efficient simpler execution plans that optimiser is able to generate.

On the other hand, in Postgres CTE is an "optimisation fence" and engine can't push predicates across CTE boundary.

Sometimes one way is better, sometimes another. Once the query complexity grows beyond certain threshold an optimiser can't analyse all possible ways to process the data and it has to settle on something. For example, the order in which to join the tables. The number of permutations grows exponentially with the number of tables to choose from. Optimiser has limited time to generate a plan, so it may make a poor choice when all CTEs are inlined. When you manually break complex query into smaller simpler ones you need to understand what you are doing, but optimiser has a better chance to generate a good plan for each simple query.

---

Related Topics