14

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

Execution plan for the temp table version Execution plan

Improve this question
0

2 Answers 2

Reset to default
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.

Improve this answer
6
  • It was a great suggestion, though I though that you couldn't use EXEC on a function....guess I was wrong
    – Lamak
    Feb 20, 2012 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, 2012 at 16:58
  • Ah, good to know, thanks for the clarification
    – Lamak
    Feb 20, 2012 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, 2012 at 22:17
  • 1
    @munissor, then don't use a table valued function. If you want better advice post exactly what you are doing.
    – HLGEM
    Feb 21, 2012 at 14:28
-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.

Improve this answer
2
  • How would that affect an insert into a table variable?
    – Martin Smith
    Jan 25, 2013 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, 2013 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.