Which are more performant, CTE or temporary tables?

From my experience in SQL Server,I found one of the scenarios where CTE outperformed Temp table

I needed to use a DataSet(~100000) from a complex Query just ONCE in my stored Procedure.

- Temp table was causing an overhead on SQL where my Procedure was
performing slowly(as Temp Tables are real materialized tables that
exist in tempdb and Persist for the life of my current procedure)

- On the other hand, with CTE, CTE Persist only until the following
query is run. So, CTE is a handy in-memory structure with limited
Scope. CTEs don't use tempdb by default.

This is one scenario where CTEs can really help simplify your code and Outperform Temp Table.
I had Used 2 CTEs, something like

WITH CTE1(ID, Name, Display)
AS (SELECT ID,Name,Display from Table1 where <Some Condition>),
CTE2(ID,Name,<col3>) AS (SELECT ID, Name,<> FROM CTE1 INNER JOIN Table2 <Some Condition>)
SELECT CTE2.ID,CTE2.<col3>
FROM CTE2
GO

So the query I was assigned to optimize was written with two CTEs in SQL server. It was taking 28sec.

I spent two minutes converting them to temp tables and the query took 3 seconds

I added an index to the temp table on the field it was being joined on and got it down to 2 seconds

Three minutes of work and now its running 12x faster all by removing CTE. I personally will not use CTEs ever they are tougher to debug as well.

The crazy thing is the CTEs were both only used once and still putting an index on them proved to be 50% faster.

It depends.

First of all

**What is a Common Table Expression?**

A (non recursive) CTE is treated very similarly to other constructs that can also be used as inline table expressions in SQL Server. Derived tables, Views, and inline table valued functions. Note that whilst BOL says that a CTE "can be thought of as temporary result set" this is a purely logical description. More often than not it is not materlialized in its own right.


**What is a temporary table?**

This is a collection of rows stored on data pages in tempdb. The data pages may reside partially or entirely in memory. Additionally the temporary table may be indexed and have column statistics.

**Test Data**


CREATE TABLE T(A INT IDENTITY PRIMARY KEY, B INT , F CHAR(8000) NULL);

INSERT INTO T(B)
SELECT TOP (1000000) 0 + CAST(NEWID() AS BINARY(4))
FROM master..spt_values v1,
master..spt_values v2;

**Example 1**

WITH CTE1 AS
(
SELECT A,
ABS(B) AS Abs_B,
F
FROM T
)
SELECT *
FROM CTE1
WHERE A = 780

![Plan 1][1]

Notice in the plan above there is no mention of CTE1. It just accesses the base tables directly and is treated the same as

SELECT A,
ABS(B) AS Abs_B,
F
FROM T
WHERE A = 780

Rewriting by materializing the CTE into an intermediate temporary table here would be massively counter productive.

Materializing the CTE definition of

SELECT A,
ABS(B) AS Abs_B,
F
FROM T

Would involve copying about 8GB of data into a temporary table then there is still the overhead of selecting from it too.

**Example 2**

WITH CTE2
AS (SELECT *,
ROW_NUMBER() OVER (ORDER BY A) AS RN
FROM T
WHERE B % 100000 = 0)
SELECT *
FROM CTE2 T1
CROSS APPLY (SELECT TOP (1) *
FROM CTE2 T2
WHERE T2.A > T1.A
ORDER BY T2.A) CA

The above example takes about 4 minutes on my machine.

Only 15 rows of the 1,000,000 randomly generated values match the predicate but the expensive table scan happens 16 times to locate these.

![enter image description here][2]

This would be a good candidate for materializing the intermediate result. The equivalent temp table rewrite took 25 seconds.

INSERT INTO #T
SELECT *,
ROW_NUMBER() OVER (ORDER BY A) AS RN
FROM T
WHERE B % 100000 = 0

SELECT *
FROM #T T1
CROSS APPLY (SELECT TOP (1) *
FROM #T T2
WHERE T2.A > T1.A
ORDER BY T2.A) CA


![With Plan][3]

Intermediate materialisation of part of a query into a temporary table can sometimes be useful even if it is only evaluated once - when it allows the rest of the query to be recompiled taking advantage of statistics on the materialized result. An example of this approach is in the SQL Cat article [When To Break Down Complex Queries][4].

In some circumstances SQL Server will use a spool to cache an intermediate result, e.g. of a CTE, and avoid having to re-evaluate that sub tree. This is discussed in the (migrated) Connect item [Provide a hint to force intermediate materialization of CTEs or derived tables][5]. However no statistics are created on this and even if the number of spooled rows was to be hugely different from estimated is not possible for the in progress execution plan to dynamically adapt in response (at least in current versions. Adaptive Query Plans may become possible in the future).


[1]:

[2]:

[3]:

[4]:

[To see links please register here]

[5]:

[To see links please register here]