Is using Table variables faster than temp tables

Question

Am I safe to assume that where I have stored procedures using the tempdb to write a temporary table, I'd be better off switching these to table variables to get better performance?

Answer 1

Temp tables are better in performance. If you use a Table Variable and the Data in the Variable gets too big, the SQL Server converts the Variable automatically into a temp table.

It depends, like almost every Database related question, on what you try to do. So it is hard to answer without more information.

So my answer is, try it and have a look at the execution plan. Use the fastest way with the lowest costs.

• MSDN - Displaying Graphical Execution Plans (SQL Server Management Studio)

Answer 2

Table variables can perform very poorly as the number of rows in them increases.

Why is this?

Table variables don’t have distribution statistics and don’t trigger recompiles. Because of this, SQL Server is not able to estimate the number of rows in a table variable like it does for normal tables. When the optimiser compiles code that contains a table variable, it assumes a table is empty and uses an expected row count of 1 for the cardinality estimate. Because the optimiser only thinks a table variable contains a single row, it picks operators for the execution plan that work well with a small set of records, like the NESTED LOOPS operator for a JOIN operation.

As an example, I have just fixed a stored procedure which was performing poorly. The code was populating a table variable and using it in a join to filter the number of rows to accounts which were relevant:

FROM dbo.DimInvestorAccount
INNER JOIN @accounts acclist 
    ON acclist.AccountNumber = DimInvestorAccount.investorAccountNumber
+ 9 additional tables joined...

When run for list of 1700 accounts, the query was taking 1m17s. Just changing the filter table definition from:

DECLARE @accounts TABLE (AccountNumber VARCHAR(20) COLLATE Latin1_General_BIN INDEX idx NONCLUSTERED)

to

CREATE TABLE #accounts (AccountNumber VARCHAR(20) COLLATE Latin1_General_BIN INDEX idx NONCLUSTERED)

brought the query time down to 800ms. Note that with 5 rows in the table, there was no significant difference - both temp table and table variable run in +/-400ms.

Microsoft's recommendation is to use Table Variables if the number of rows is <100.

Note that Microsoft have made changes in SQL Server 2019 to improve this (v15.x/Compatibility level 150)

Answer 3

@Table can be faster as there is less "setup time" since the object is in memory only.

@Tables have a lot of catches though.

You can have a primary key on a @Table but thats about it. Other indexes Clustered NonClustered for combinations of columns are not possible.

Also if your table is going to contain any real data volumes (more then about 200 maybe 1000 rows) then accessing the table will be slower. Especially when you will probably not have a useful index on it.

#Tables are a pain in procs as they need to be dropped when debugging, They take longer to create. and they take longer to setup as you need to add indexs as a second step. But if you have lots of data then its #tables every time.

Even in cases where you have less then 100 rows of data in a table you may still want to use #Tables as you can create a usefull index on the table.

In summary i use @Tables most of the time for the ease when doing simple proc etc. But anything that need to perform should be a #Table.

Answer 4

@Tables have no statistics so the execution plan entails more guesswork. Hence the recommended upper limit of 1000-ish rows. #Tables have statistics but these can be cached between invocations. If your cardinalities differ significantly each time the SP runs you'd want to REBUILD and RECOMPILE each time. This is an overhead, of course, but one which must be balanced against the cost of a rubbish plan.

Both types will do IO to TempDB.

So no, @Tables are not a panacea.