INSERT performance difference between temporary tables and table variable

Question

I have the following issue in SQL Server 2005: trying to inserts some rows into a table variable takes a lot of time compared to the same insert using a temporary table.

This is the code to insert into the table variable

DECLARE @Data TABLE(...)
INSERT INTO @DATA( ... )
SELECT ..
FROM ...

This is the code to insert into the temp table

CREATE #Data TABLE(...)
INSERT INTO #DATA( ... )
SELECT ..
FROM ...
DROP TABLE #Data

The temporary table doesn't have any keys or indexes, the select part is the same between the 2 queries, and the number of results returned by the select is ~10000 rows. The time needed to execute the select alone is ~10 seconds.

The temp table version takes up to 10 seconds to execute, I had to stop the table variable version after 5 minutes.

I have to use a table variable because the query is part of a table value function, that doesn't allow access to temporary table.

Execution plan for the table variable version

Execution plan for the temp table version

score 9 · Accepted Answer · answered Feb 20 '12 at 16:41

9

The obvious difference between the two plans is that the fast one is parallel and the slower one serial.

This is one of the limitations of plans that insert into table variables. As mentioned in the comments (and it seems as though it had the desired effect) you could try doing

INSERT INTO @DATA ( ... ) 
EXEC('SELECT .. FROM ...')

to see if that gets around the limitation.

answered Feb 20 '12 at 16:41

Martin Smith

84,644
15
245
333

It was a great suggestion, though I though that you couldn't use EXEC on a function....guess I was wrong – Lamak Feb 20 '12 at 16:55
1

@Lamak - Doh! You can't so this won't work for the OP. Invalid use of a side-effecting operator 'INSERT EXEC' within a function.. The OPENQUERY work around might work though. – Martin Smith Feb 20 '12 at 16:58
Ah, good to know, thanks for the clarification – Lamak Feb 20 '12 at 18:06
2

As a general rule of thumb, you don't want to use table variables if you will be expecting to get a large data aset returned. Temp tables are usually faster inthis case. – HLGEM Feb 20 '12 at 22:17
@HLGEM: you cannot use temp tables in table value functions. – munissor Feb 21 '12 at 08:58
1

@munissor, then don't use a table valued function. If you want better advice post exactly what you are doing. – HLGEM Feb 21 '12 at 14:28

score -1 · Answer 2 · answered Jan 24 '13 at 17:39

-1

Table variables are sometimes slower because there are no statistics on table variables, and thus the optimizer always assumes only one record.

However I cannot guarantee that this is the case here, you will have to take a look on the "estimated rows" info in the query plan for the table variable.

answered Jan 24 '13 at 17:39

yoel halb

319
2
9

How would that affect an insert into a table variable? – Martin Smith Jan 25 '13 at 11:18
That's what appears to be going on, as you can see that there is not just a difference between parallel and serial but also between hash and nested loop joins, apparently the optimizer assumes that since the table variable holds one record in its mind then the result of the query will also be one record, once again the only way to prove it would be to see the actual stats for each part of the query, but the fact is that all queries involving table variables end up with loop joins and serial processing, so I thing it is fair to suspect it here – yoel halb Jan 27 '13 at 20:01

INSERT performance difference between temporary tables and table variable

2 Answers2

Linked