Sql-server – INSERT performance difference between temporary tables and table variable

performancesql-server-2005temporary-tables

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

Best Answer

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.

Related Question