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.

SQL SERVER - Table Variables or Temp Tables - Performance Comparison - SELECT table-variable-800x277

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

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.

SQL SERVER - Table Variables or Temp Tables - Performance Comparison - SELECT selecttempvars

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)

, , , ,
Previous Post
SQL SERVER – Check for Update in SSMS
Next Post
SQL SERVER – Script to Get Partition Info Using DMV

Related Posts

4 Comments. Leave new

  • Really good. In your example if you separate out both scripts and run on different session I see both takes same amount of time. At times # table takes more time. I agree # table performs better than table variable, in fact I have changes many place from table variable to temp tables.

    Check this:

    — 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;
    — —————————–
    — Performance Co mparision
    SET STATISTICS IO,TIME ON;
    SELECT *
    FROM #FirstIndex
    WHERE City = ‘Las Vegas’;
    SET STATISTICS TIME OFF;
    — Clean up
    DROP TABLE #FirstIndex
    GO

    —————————————————————————————————————–

    — 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’;

    SET STATISTICS TIME OFF;
    GO

    Reply
  • I was wondering if you considered creating an index on City for both the table variable and temp table for your comparison. In my testing with your script, the difference was about 140 logical reads for the temp table via the new index vs the same 4.5k reads for the table variable. So, while you can create an index on City for the table variable, it doesn’t seem to be utilized by the query.

    Reply
  • Another thing I wonder if you’ve researched is using data compression with temp tables. Using your script with data compression I ended up with 1379 logical reads instead of the 4546 from the script in this article. On my test machine this ended up adding about 10-20 ms of server time overhead, so it looks like it would be a tradeoff between lowering I/O against tempdb and slightly more CPU time for decompressing the data.

    Reply

Leave a Reply

Menu