SQL SERVER – Table Variables or Temp Tables – Performance Comparison – SELECT

This is the fourth post in the series which I have been writing about the Table Variables and Temp Table. Today we are going to discuss the performance comparison of table variables and temp tables when we are running SELECT queries. I usually discuss this topic in detail at my Comprehensive Database Performance Health Check.

Before you continue reading this blog post I suggest you read the following blog posts:

• SQL SERVER – Regular Table or Temp Table – TempDB Logging Explained
• SQL SERVER – Regular Table or Temp Table – A Quick Performance Comparison
• SQL SERVER – Table Variable or Temp Table – Performance Comparison – INSERT
• Where is Table Variable Created? – Interview Question of the Week #243

In an earlier post, I discussed that Table Variables performs as Temp Table in case of the INSERT statement. Additionally, I also blogged here that table variables are created in the TempDB just like a Temp Table. One of the users followed up this series of the post and asked what is the purpose of the Table Variables when they behave mostly like a Temp Table. Well, let me say this way, I have found Table Variable of very little use in most of the coding as they usually give poor performance when I have lots of data in my table. Let us see a quick demonstration of that topic.

Let us run the following script and first create a Temporary Table. Right after that, we will use the same data to populate our Table Variable. Following that, we will run a simple select statement and compare its performance.

USE SQLAuthority
GO
-- Create Table
CREATE TABLE #FirstIndex (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100));
-- INSERT 1
INSERT INTO #FirstIndex (ID,FirstName,LastName,City)
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%12345 = 1 THEN 'Las Vegas'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;
-- -----------------------------
-- Create Table Variable
DECLARE @FirstIndex TABLE (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100));
-- INSERT 1
INSERT INTO @FirstIndex (ID,FirstName,LastName,City)
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%12345 = 1 THEN 'Las Vegas'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;
-- --------------------------------------------
-- Performance Co mparision
SET STATISTICS IO,TIME ON;
SELECT *
FROM #FirstIndex
WHERE City = 'Las Vegas';
SELECT *
FROM @FirstIndex
WHERE City = 'Las Vegas';
SET STATISTICS TIME OFF;
-- Clean up
DROP TABLE #FirstIndex
GO

Please make sure that you run the above script in a SINGLE execution or it will give an error. Table Variables have to be created and consumed in the same sessions.

Now when we observe the Statistics IO or statistics Time there is not much difference between them.

For Temp Table:
Table ‘#FirstIndex00000000000C’. Scan count 1, logical reads 4546
SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 60 ms.

For Table Variable:
Table ‘#B8CBF7CD’. Scan count 1, logical reads 4546, physical reads 0
SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 74 ms.

However, when we see the execution plan we see that table variables are not estimating the rows to be returned correctly and they show value as 1, whereas in the case of the Temp Table we are getting a correct estimation.

I have worked with hundreds of the clients and my experience says whenever I have used temp table I have always seen a little performance improvement and that is why I always suggest my clients use Temporary Tables over Table Variables.

Reference: Pinal Dave (https://blog.sqlauthority.com)