[sql-server] Which are more performant, CTE or temporary tables?

Which are more performant, CTE or Temporary Tables?

Late to the party, but...

The environment I work in is highly constrained, supporting some vendor products and providing "value-added" services like reporting. Due to policy and contract limitations, I am not usually allowed the luxury of separate table/data space and/or the ability to create permanent code [it gets a little better, depending upon the application].

IOW, I can't usually develop a stored procedure or UDFs or temp tables, etc. I pretty much have to do everything through MY application interface (Crystal Reports - add/link tables, set where clauses from w/in CR, etc.). One SMALL saving grace is that Crystal allows me to use COMMANDS (as well as SQL Expressions). Some things that aren't efficient through the regular add/link tables capability can be done by defining a SQL Command. I use CTEs through that and have gotten very good results "remotely". CTEs also help w/ report maintenance, not requiring that code be developed, handed to a DBA to compile, encrypt, transfer, install, and then require multiple-level testing. I can do CTEs through the local interface.

The down side of using CTEs w/ CR is, each report is separate. Each CTE must be maintained for each report. Where I can do SPs and UDFs, I can develop something that can be used by multiple reports, requiring only linking to the SP and passing parameters as if you were working on a regular table. CR is not really good at handling parameters into SQL Commands, so that aspect of the CR/CTE aspect can be lacking. In those cases, I usually try to define the CTE to return enough data (but not ALL data), and then use the record selection capabilities in CR to slice and dice that.

So... my vote is for CTEs (until I get my data space).

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

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.

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 

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.

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. 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).

CTE has its uses - when data in the CTE is small and there is strong readability improvement as with the case in recursive tables. However, its performance is certainly no better than table variables and when one is dealing with very large tables, temporary tables significantly outperform CTE. This is because you cannot define indices on a CTE and when you have large amount of data that requires joining with another table (CTE is simply like a macro). If you are joining multiple tables with millions of rows of records in each, CTE will perform significantly worse than temporary tables.

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.

I just tested this- both CTE and non-CTE (where the query was typed out for every union instance) both took ~31 seconds. CTE made the code much more readable though- cut it down from 241 to 130 lines which is very nice. Temp table on the other hand cut it down to 132 lines, and took FIVE SECONDS to run. No joke. all of this testing was cached- the queries were all run multiple times before.

This is a really open ended question, and it all depends on how its being used and the type of temp table (Table variable or traditional table).

A traditional temp table stores the data in the temp DB, which does slow down the temp tables; however table variables do not.

One use where I found CTE's excelled performance wise was where I needed to join a relatively complex Query on to a few tables which had a few million rows each.

I used the CTE to first select the subset based of the indexed columns to first cut these tables down to a few thousand relevant rows each and then joined the CTE to my main query. This exponentially reduced the runtime of my query.

Whilst results for the CTE are not cached and table variables might have been a better choice I really just wanted to try them out and found the fit the above scenario.

CTE won't take any physical space. It is just a result set we can use join.

Temp tables are temporary. We can create indexes, constrains as like normal tables for that we need to define all variables.

Temp table's scope only within the session. EX: Open two SQL query window

create table #temp(empid int,empname varchar)
insert into #temp 
select 101,'xxx'

select * from #temp

Run this query in first window then run the below query in second window you can find the difference.

select * from #temp

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

Temp tables are always on disk - so as long as your CTE can be held in memory, it would most likely be faster (like a table variable, too).

But then again, if the data load of your CTE (or temp table variable) gets too big, it'll be stored on disk, too, so there's no big benefit.

In general, I prefer a CTE over a temp table since it's gone after I used it. I don't need to think about dropping it explicitly or anything.

So, no clear answer in the end, but personally, I would prefer CTE over temp tables.

I've used both but in massive complex procedures have always found temp tables better to work with and more methodical. CTEs have their uses but generally with small data.

For example I've created sprocs that come back with results of large calculations in 15 seconds yet convert this code to run in a CTE and have seen it run in excess of 8 minutes to achieve the same results.