CTE (Common Table Expression) vs Temp tables or Table variables, which is faster?

Question

CTE (Common Table Expression) vs Temp tables or Table variables, which is faster?

Pendulous

Easy: IT DEPENDS! - there's NO one single answer to this - it really does depend on your data (how much? What kind?), how complex your CTE is..... As is, this is impossible to answer, really.... – Cismontane

@marc_s: I currently have a Table variable based solution, which I was thinking of changing to a CTE, the existing query is with 3 joins and a lot of date based checks. So I thought if CTE's would improve anything? – Pendulous

@System.Expection: can you show us your query?? Again: without knowing what exactly you're doing it's impossible to make any really useful comment... – Cismontane

Answer 1

We got a 50% increase in speed moving to CTE in one particular case so it's worth giving it a go but any performance related enhancements need to be bench marked so you can compare one against another.

PS: we wrote more than one query with a CTE in it before we got the one we now use.

Circumstantiate

Answer 2

As I already said in my comment: IT DEPENDS!

It really does depend on your query, your data (how much is there? What kind is it??) and much more.

A few bullet points to remember, though:

• a CTE is an "inline view" valid only for the next statement; if possible, SQL Server will keep a temporary result in memory

• Temp Tables come in two flavours: those visible to your connection only (create table #temp), or those that are globally visible to all connections (create table ##temp); both will be auto-dropped when no connection is using them anymore. You can define indices on temp tables, and they're part of transactions

• Table variables don't allow you to create indices on them, nor do they participate in the transactional dance - this can be a pro or a con - just be aware of it! A ROLLBACK has no effect on the data contained in a table variable....

Cismontane

Answer 3

http://royalarun.blogspot.in/2013/10/difference-between-cte-and-temp-table.html

Difference between CTE and Temp Table and Table Variable in SQL Server

1. Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables. It is divided into two Local temp tables and Global Temp Table, Local Temp table are only available to the SQL Server session or connection (means single user) that created the tables. Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed.cases where you need transaction rollback support.

2. CTE - Common table Expression is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.

3. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped automatically once it comes out of batch. This is also created in the Tempdb database but not the memory.You cannot create a non-clustered index on a table variable, unless the index is a side effect of a PRIMARY KEY or UNIQUE constraint on the table. If the resultset is small, the table variable is always the optimum choice.

Thorstein