SQL Server Performance Tuning : Table Variable Vs Temporary Tables


We have quite often used temporary objects (Table Variables and Temporary Tables) for setting aside intermediate results. Both have their advantages and disadvantages. So why use them at the first place? Well, in number of cases, complex queries involving many tables with sub-optimal query plans when broken down into smaller set of individual queries involving lesser joins helps in performance. The output from these smaller joins can be stored in a temporary objects and reused in other queries. This also helps specifically when repeated computations are involved.

Now what should be our choice - a Temporary Table or a Table Variable?

Let us walk through this with an example. Take a scenario where we want to fetch the count of transactions  in a current year for different products on AdventureWorks2012 Database. In addition, we would also like to show the no of transactions for last couple of years.

Query1#Start#:

;With BASE AS
(
SELECT ProductID, YEAR(TransactionDate) AS TransCurrYear, COUNT(1) AS NoTrans
FROM Production.TransactionHistory
GROUP BY ProductID, YEAR(TransactionDate)
)
SELECT CurrYear.ProductID, CurrYear.NoTrans AS CurrTransCnt, PrevYear.NoTrans AS PrevTransCnt, Prev2Year.NoTrans AS Prev2YearCnt
FROM BASE AS CurrYear
CROSS APPLY (SELECT *
FROM BASE PrevYear
WHERE CurrYear.ProductID = PrevYear.ProductID
AND CurrYear.TransCurrYear = PrevYear.TransCurrYear - 1 ) AS PrevYear
OUTER APPLY (SELECT *
FROM BASE Prev2Year
WHERE CurrYear.ProductID = Prev2Year.ProductID
AND CurrYear.TransCurrYear = Prev2Year.TransCurrYear - 2 ) AS Prev2Year

 

Query1#End#:

 

Observations from Query 1:

 

  1. There were over 27 scans with logical reads of 1998
  2. Although we used a CTE Operation, we could see that similar aggregations are being repeated in the plan.

 

Let's now use a Temporary Table.

 

Query2#Start#:

 

CREATE TABLE #T1
(ProductID int
,TransCurrYear int
,NoTrans int
);

CREATE CLUSTERED INDEX CI_#T1 ON #T1 (TransCurrYear)

INSERT INTO #T1
SELECT ProductID, YEAR(TransactionDate) AS TransCurrYear, COUNT(1) AS NoTrans
FROM Production.TransactionHistory
GROUP BY ProductID, YEAR(TransactionDate)
ORDER BY YEAR(TransactionDate)

;With BASE AS
(
SELECT * FROM #T1
)
SELECT CurrYear.ProductID, CurrYear.NoTrans AS CurrTransCnt, PrevYear.NoTrans AS PrevTransCnt, Prev2Year.NoTrans AS Prev2YearCnt
FROM BASE AS CurrYear
CROSS APPLY (SELECT *
FROM BASE PrevYear
WHERE CurrYear.ProductID = PrevYear.ProductID
AND CurrYear.TransCurrYear = PrevYear.TransCurrYear - 1 ) AS PrevYear
OUTER APPLY (SELECT *
FROM BASE Prev2Year
WHERE CurrYear.ProductID = Prev2Year.ProductID
AND CurrYear.TransCurrYear = Prev2Year.TransCurrYear - 2 ) AS Prev2Year

 

 

 

 

Query2#End#:

 

Observations from Query 2:

 

  1. Logical Reads are down when compared to Query1.
  2. No Repetition of computation of Aggregated Values.
  3. SQL Server uses statistics as can be seen from the properties above which is good when data is more.

 

Great. Let's now do it with a table variable

 

Query3#Start#:

 

DECLARE @T1 AS TABLE
(ProductID int
,TransCurrYear int
,NoTrans int
, INDEX [IX_TransactionYear] CLUSTERED (ProductID,TransCurrYear)
);


INSERT INTO @T1
SELECT ProductID, YEAR(TransactionDate) AS TransCurrYear, COUNT(1) AS NoTrans
FROM Production.TransactionHistory
GROUP BY ProductID, YEAR(TransactionDate)
ORDER BY YEAR(TransactionDate)

;With BASE AS
(
SELECT * FROM @T1
)
SELECT CurrYear.ProductID, CurrYear.NoTrans AS CurrTransCnt, PrevYear.NoTrans AS PrevTransCnt, Prev2Year.NoTrans AS Prev2YearCnt
FROM BASE AS CurrYear
CROSS APPLY (SELECT *
FROM BASE PrevYear
WHERE CurrYear.ProductID = PrevYear.ProductID
AND CurrYear.TransCurrYear = PrevYear.TransCurrYear - 1 ) AS PrevYear
OUTER APPLY (SELECT *
FROM BASE Prev2Year
WHERE CurrYear.ProductID = Prev2Year.ProductID
AND CurrYear.TransCurrYear = Prev2Year.TransCurrYear - 2 ) AS Prev2Year

 

 

 

Query3#End#: 

 

Observations from Query3:

 

  1. Scan Count and Logical Reads are slightly up.
  2. There is no statistics associated with Clustered Key Creation in Table Variable which can be seen from the Estimated Number of Rows Value. This could be bad when data is more.

 

One reason why the Optimizer could not sniff the "Estimated Number of Rows" is because the entire batch query populates the variable table followed by querying on it. And hence, it is not able to figure out the cardinality. If we add the RECOMPILE Option to the query, SQL Server is able to detect the cardinality like so,

 

Query4#Start#:

 

;With BASE AS
(
SELECT * FROM @T1
)
SELECT CurrYear.ProductID, CurrYear.NoTrans AS CurrTransCnt, PrevYear.NoTrans AS PrevTransCnt, Prev2Year.NoTrans AS Prev2YearCnt
FROM BASE AS CurrYear
CROSS APPLY (SELECT *
FROM BASE PrevYear
WHERE CurrYear.ProductID = PrevYear.ProductID
AND CurrYear.TransCurrYear = PrevYear.TransCurrYear - 1 ) AS PrevYear
OUTER APPLY (SELECT *
FROM BASE Prev2Year
WHERE CurrYear.ProductID = Prev2Year.ProductID
AND CurrYear.TransCurrYear = Prev2Year.TransCurrYear - 2 ) AS Prev2Year
OPTION (RECOMPILE)

 

 

Query4#End#:

One misconception we often hear is that Table Variables are always in memory and it does not incur any disk activity. That was never the case. With larger set of data, there can always be spill over to TempDB.

However, with SQL Server 2014, Table Variables can be defined as a Memory Optimized one by using the option of (MEMORY_OPTIMIZED = TRUE) .

Query5#Start#:

 

CREATE TYPE [ProductYearlyFigureType] AS TABLE(
ProductID int NOT NULL
,TransCurrYear int NOT NULL
,NoTrans int
, INDEX [IX_TransactionYear] NONCLUSTERED (ProductID,TransCurrYear)
)
WITH ( MEMORY_OPTIMIZED = ON );


DECLARE @T1 AS [ProductYearlyFigureType];


INSERT INTO @T1
SELECT ProductID, YEAR(TransactionDate) AS TransCurrYear, COUNT(1) AS NoTrans
FROM Production.TransactionHistory
GROUP BY ProductID, YEAR(TransactionDate)
ORDER BY YEAR(TransactionDate)

;With BASE AS
(
SELECT * FROM @T1
)
SELECT CurrYear.ProductID, CurrYear.NoTrans AS CurrTransCnt, PrevYear.NoTrans AS PrevTransCnt, Prev2Year.NoTrans AS Prev2YearCnt
FROM BASE AS CurrYear
CROSS APPLY (SELECT *
FROM BASE PrevYear
WHERE CurrYear.ProductID = PrevYear.ProductID
AND CurrYear.TransCurrYear = PrevYear.TransCurrYear - 1 ) AS PrevYear
OUTER APPLY (SELECT *
FROM BASE Prev2Year
WHERE CurrYear.ProductID = Prev2Year.ProductID
AND CurrYear.TransCurrYear = Prev2Year.TransCurrYear - 2 ) AS Prev2Year

 

 

 

Query5#End#:

Observations from Query5:

  1. Scan Count and Logical Reads are only to read from base table.
  2. There is no Physical IO involved when reading from memory optimized tables.   

Apart from this, there are few other differences between a Table Variable and a Temporary Table

  1. Scope of a Table Variable is only within the batch that declared it whereas Temporary Tables are visible within a session.
  2. Creation of too many Temporary Tables may result in contention of the SGAM Pages.
  3. In events of rollback, data  processing in table variables (within the transaction) are not rolled back or destroyed unlike the temporary tables.

Hope this helps to de-mystify the difference between the two. Thanks

Comments (3)

  1. FLauffer says:

    Nice post! Great tip about the difference of these two objects..

  2. Oz says:

    Cant see the most of the small text Can you upload better quality images

  3. Henry says:

    An important difference is that table variable changes do not get rolled back during a transaction ROLLBACK whereas a temp table will. See here http://www.linkedin.com/…/highlighted-differences-between-sql-server-temporary-tables-andrew

Skip to main content