Which are more performant CTE or temporary tables

Question

Which are more performant  CTE or Temporary Tables

User · Answer

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.

User · Answer

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  lt Some Condition gt    CTE2 ID Name  lt col3 gt   AS  SELECT ID  Name  lt  gt  FROM CTE1 INNER JOIN Table2  lt Some Condition gt   SELECT CTE2 ID CTE2  lt col3 gt  FROM CTE2 GO

User · Answer

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  gt  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  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I d say they are different concepts but not too different to say  chalk and cheese     A temp table is good for re-use or to perform multiple processing passes on a set of data  A CTE can be used either to recurse or to simply improved readability   And  like a view or inline table valued function can also be treated like a macro to be expanded in the main query A temp table is another table with some rules around scope   I have stored procs where I use both  and table variables too

User · Answer

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

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

Examples related to sql-server

Examples related to performance

Examples related to sql-server-2008

Examples related to temp-tables

Examples related to common-table-expression