Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

up vote 7 down vote accepted

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.

share|improve this answer
    
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
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
show 1 more comment

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.

share|improve this answer
    
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 –  yo hal Jan 27 '13 at 20:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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