mssqltips logo

SQL Server CTE vs Temp Table vs Table Variable Performance Test

By:   |   Updated: 2018-09-04   |   Comments (6)   |   Related: More > T-SQL

Problem

In one of my previous tips we looked at the SQL Server performance differences between using a temp table and a table variable for a few different DML operations.  One of the comments suggested comparing these results to using a Common Table Expression (CTE) for similar operations.  In this tip we will go through a few simple SELECT queries in order to compare the performance in SQL Server using each type of object, temp table, table variable and a CTE, to store the temporary data.  We will compare the execution times of each query as well as the system resources needed to complete each operation.

Solution

While a CTE is a really good tool it does have some limitations as compared with a temporary table or a table variable.  This biggest difference is that a CTE can only be used in the current query scope whereas a temporary table or table variable can exist for the entire duration of the session allowing you to perform many different DML operations against them.  That said the CTE is still a really useful tool which can make your T-SQL code more readable as well as makes writing recursive queries much less complex as the CTE can reference itself. 

For this test we will go through an example using the Adventureworks2014 database and gather all the order header data for each customers most recent order.  Below is the T-SQL for each of our test query types.

-- CTE
WITH t (customerid,lastorderdate) AS 
(SELECT customerid,max(orderdate) FROM sales.SalesOrderHeader
 GROUP BY customerid)
SELECT * FROM sales.salesorderheader soh
INNER JOIN t ON soh.customerid=t.customerid AND soh.orderdate=t.lastorderdate
GO

-- Temp table
CREATE TABLE #temptable (customerid [int] NOT NULL PRIMARY KEY,lastorderdate [datetime] NULL);
INSERT INTO #temptable
SELECT customerid,max(orderdate) lastorderdate FROM sales.SalesOrderHeader
 GROUP BY customerid;
SELECT * from sales.salesorderheader soh
INNER JOIN #temptable t ON soh.customerid=t.customerid AND soh.orderdate=t.lastorderdate
drop table #temptable
GO

-- Table variable
DECLARE @tablevariable TABLE (customerid [int] NOT NULL PRIMARY KEY,lastorderdate [datetime] NULL);
INSERT INTO @tablevariable
SELECT customerid,max(orderdate) lastorderdate FROM sales.SalesOrderHeader
 GROUP BY customerid;
SELECT * from sales.salesorderheader soh
INNER JOIN @tablevariable t ON soh.customerid=t.customerid AND soh.orderdate=t.lastorderdate
GO

Looking at the SQL Profiler results from these queries (each were run 10 times and averages are below) we can see that the CTE just slightly outperforms both the temporary table and table variable queries when it comes to overall duration.  It also uses less CPU than both of them and performs fewer reads (significant fewer reads that the table variable query).

Query Type Reads Writes CPU Duration (ms)
CTE 1378 0 47 497
Temp table 2146 51 109 544
Table variable 133748 51 297 578

Let's also test getting the most recent order header data for just a single customer to see if there is any difference in performance for a smaller data set. Here are the updated queries which add a WHERE clause to each statement we tested above.

-- CTE
WITH t (customerid,lastorderdate) AS 
(SELECT customerid,max(orderdate) FROM sales.SalesOrderHeader
 WHERE customerid=27604 
 GROUP BY customerid)
SELECT * from sales.salesorderheader soh
INNER JOIN t ON soh.customerid=t.customerid AND soh.orderdate=t.lastorderdate
GO

--Temp table
CREATE TABLE #temptable (customerid [int] NOT NULL PRIMARY KEY,lastorderdate [datetime] NULL);
INSERT INTO #temptable
SELECT customerid,max(orderdate) lastorderdate FROM sales.SalesOrderHeader
 WHERE customerid=27604
 GROUP BY customerid;
SELECT * from sales.salesorderheader soh
INNER JOIN #temptable t ON soh.customerid=t.customerid AND soh.orderdate=t.lastorderdate
drop table #temptable
GO

--Table variable
DECLARE @tablevariable TABLE (customerid [int] NOT NULL PRIMARY KEY,lastorderdate [datetime] NULL);
INSERT INTO @tablevariable
SELECT customerid,max(orderdate) lastorderdate FROM sales.SalesOrderHeader
 WHERE customerid=27604
 GROUP BY customerid;
SELECT * from sales.salesorderheader soh
INNER JOIN @tablevariable t ON soh.customerid=t.customerid AND soh.orderdate=t.lastorderdate
GO

Looking at these SQL Profiler results (averaged over 10 executions) we again see that the CTE actually gives us the best overall performance in terms of duration and resources used.  The surprising thing in this case though is that the table variable outperformed the temporary table in all categories.

Query Type Reads Writes CPU Duration (ms)
CTE 10 0 0 1
Temp table 318 1 15 12
Table variable 91 1 0 1

As mentioned at the start of the article, a CTE is not quite as versatile as temporary tables and table variables, but we’ve shown here that for individual queries that require temporary/staged data it can be a good option to improve performance.  Remember though when dealing with query performance to always test with your own queries/data as there are many variables that can affect performance and different use cases could lead to another option giving better performance.

Next Steps


Last Updated: 2018-09-04


get scripts

next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, February 27, 2019 - 9:53:00 AM - abc Back To Top

Its good article shows all the temp variable and Temp table and CTE differences clearly


Thursday, January 10, 2019 - 2:54:54 PM - Michael S Barash Back To Top

 Ben, I appreciate this aritcle.  I have been able to greatly reduce the number of reads on declared tables by putting an index on them.  Most of the extra reads were due to a default table-scan on my declared tables.  If we can greatly reduce the number of reads on table-variable with indexes, then this makes table-variables perform even better.

Thanks, Michael Barash - DBA/Senior-SQL-Developer.


Tuesday, September 04, 2018 - 11:14:22 AM - R Evans Back To Top

The speed will depend on the data.  (I haven't got AdventureWorks 2014 to hand to say how many rows would be involved in this example.)

Table variables don't have statistics so the cardinality estimator assumes there is only one row - so these work best with smaller datasets.


Tuesday, September 04, 2018 - 10:36:37 AM - Adrian Hernandez Back To Top

 Hello,

Based on your experience, would you say that CTE is always faster that using a table variable or a temp table?


Tuesday, September 04, 2018 - 9:47:01 AM - Ben Snaidero Back To Top

@R Evans

Good idea.  Actually just needed a small update to your TSQL and it executed using only 5 reads (as opposed to 10 in my example).  Updated TSQL is below.

WITH t ([SalesOrderID],[RevisionNumber],[OrderDate],[DueDate],[ShipDate],[Status],[OnlineOrderFlag],[SalesOrderNumber]
      ,[PurchaseOrderNumber],[AccountNumber],[CustomerID],[SalesPersonID],[TerritoryID],[BillToAddressID],[ShipToAddressID]
      ,[ShipMethodID],[CreditCardID],[CreditCardApprovalCode],[CurrencyRateID],[SubTotal],[TaxAmt],[Freight]
      ,[TotalDue],[Comment],[rowguid],[ModifiedDate],rowno)
AS (
SELECT *, row_number() over (partition by customerid order by orderdate desc) rowno
FROM sales.SalesOrderHeader
WHERE customerid=27604
) SELECT * from t
where rowno = 1


Tuesday, September 04, 2018 - 3:54:21 AM - R Evans Back To Top

With the CTE there's no need to join back to the cte - you can use the row_number() window function; would be interesting to compare the performance again.

WITH t (customerid,lastorderdate)
AS (
SELECT *, row_number() over (partition by customerid order by orderdate desc) rowno
FROM sales.SalesOrderHeader
WHERE customerid=27604
GROUP BY customerid
) SELECT * from t
where rowno = 1



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools