Temporary table storage performance comparison

Question

Can anyone break it down in plain English the performance difference between using temp tables vs. CTE's vs. table variables in MSSQL. I have used temporary tables quite frequently and have started using CTE's just because of the clear syntax but I have found them to be slower. I think that temp tables are using system memory and that is why they seem fast but may be a bottleneck if trying to do multiple jobs. Table variables I have used sparingly and do not know a great deal about. Looking for some advice from the guru's out there!

I believe temp tables (#table) are stored in the system db, so not necessarily in memory (and also they inherit server defaults for certain properties, like collation). But the main reason I commented was to say your tag is mysql, not mssql. — tomfumb, Aug 15 '11 at 23:41

score 0 · Answer 1 · answered Aug 16 '11 at 00:39

CTEs are performance-neutral. They simplify a query for the developer by abstracting out SQL statements - usually complicated JOINs or built-in functions applied to fields. The database engine just in-lines the CTE into the query that uses it. So, the CTE itself isn't "slow", but you may find you are having better performance with temp tables because the database engine is creating better query plans on the queries using the temp tables.

score 0 · Answer 2 · edited May 23 '17 at 12:02

0

This question was answered here and here. Briefly, this is a different tools fo different tasks.

Table variables can lead to fewer stored procedure recompilations than temporary tables
A temp table is good for re-use or to perform multiple processing passes on a set of data

edited May 23 '17 at 12:02

Community

1
1

answered Aug 16 '11 at 00:56

Alex_L

2,658
1
15
13

score 0 · Accepted Answer · edited May 23 '17 at 09:59

This question is well covered in Books Online, MSDN and this site.

About temp tables and table variables you can read here What's the difference between a temp table and table variable in SQL Server?. There you will find that in many cases temp tables cause recompilation of a procedure which is their main disadvantage.

CTEs are well described here http://blogs.msdn.com/b/craigfr/archive/2007/10/18/ctes-common-table-expressions.aspx

Temporary table storage performance comparison

3 Answers3