通过

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