Table Variable Vs. Temp Table

Every time I do a performance training class I share some stories about those long running reports that I was able to speed up by simply switching a Table Variable (declared with the @ sign) to a Temp Table (declared with the # sign).

-- table variable
DECLARE @temp AS TABLE (usersInGroup INT, theGroup INT, theDay DATETIME );

-- temp table
CREATE TABLE #temp
(
usersInGroup INTEGER,
theGroup     INTEGER,
theDay       DATETIME
);

Today I was working on a batch of TSQL code that took about 8 minutes to run and used 2 table variables. It filled one in from a big query that took 3 seconds, then it ran several queries to categorize data and update the second table variable. That latter took the 8 minutes.

The first thing I tried when I saw the table variables was to quickly search and replace on @tempTableName to #tempTableName for both tables, then to change the DECLARE statement to a CREATE TABLE #tempTableName.

The run time after that simple change went from over 8 minutes to 3 seconds to run.

Why do they perform so different?

There are lots of cool things you can do with indexes on temp tables but not on table variables, but we haven’t done that.

The real difference here is the following:

The query optimizer doesn’t do a great job with table variables. It seems to assume there is always 1 row, when indeed there are more. This causes bad query optimizer decisions on larger table variables.
Table variable can have statistics on them to help with query optimizer decisions.

This is not the first time I have seen this type of a performance difference. It’s a simple switching out of temp tables for table variables about every other month. A super easy win and our customers are happier because of it.

One of the biggest improvements was a query a couple of years ago. The report developer had been working on for several weeks. It took just over 2 hours to run, it was immense with thousands of lines of code and a whole lot of business logic that I didn’t want to take the time to learn. I noticed that he was using 5 table variables in this big script. I changed them to temp tables, did a search and replace through thousands of lines of code on the table variable names to replace with temp table names. I of course added drop table for those temp tables at the end of the script. That whole process took me 5 minutes, and the run time on the query went from just over 2 hours, to just under 1 minute! WOW what a time saver!

My simple rule is DO NOT USE TABLE VARIABLES unless:

You know better, and can prove it with a comparison.
You have no other choice, for instance in a table valued function.
You know your table will always have one row in it.

The question always comes up as to “what if I don’t have permissions to create tables”. Well temp tables don’t require any special permissions to create. Any user can create temp tables.

So my recommendation is to stay away from table variables if query performance is needed.

Need help with this type of query tuning or SQL performance? We can help at Stedman Solutions, LLC.

We offer Expert Performance Assessments

Or maybe all you need is some SQL Server Mentoring.

Table Variable Vs. Temp Table

Why do they perform so different?

The real difference here is the following:

My simple rule is DO NOT USE TABLE VARIABLES unless:

Leave a Reply Cancel reply

Login