SELECT statement that executes instantly, takes forever to insert into table variable

Question

I have the following SQL code below. It is taking forever to complete but if I run just the SELECT DISTINCT ID, ParentID FROM Y WHERE ID IN (3,4), it completes instantly.

DECLARE @table TABLE
(
  ID int,
  ParentID int
)

INSERT INTO @table 
SELECT DISTINCT ID, ParentID FROM Y WHERE ID IN (3,4)

SELECT * FROM @table

What is going on, this makes no sense.

A limitation of queries that insert into table variables is that they can't use parallel plans. — Martin Smith, Jan 18 '13 at 17:28

score 2 · Accepted Answer · edited Dec 25 '12 at 18:42

2

Try to use a temporary table instead. The table variable is optimized for one row only and assumes that is what it will be getting.

Read more here

edited Dec 25 '12 at 18:42

onkar

4,427
10
52
89

answered Nov 08 '12 at 06:07

unitario

6,295
4
30
43

1

The solution of using a temporary table might work. The explanation that this is because "the table variable is optimized for one row only" is nonsense. Any difference in insert performance between the two will be because of parallel vs serial plans. – Martin Smith Jan 18 '13 at 17:29

score 1 · Answer 2 · answered Nov 08 '12 at 01:45

1

Even though it is a simple SELECT that will execute almost instantly, if you have a large dataset, the IN will take much longer because it's going to collect all of the data before processing the conditions. Use and EXISTS in it's place and it will most likely run much faster.

answered Nov 08 '12 at 01:45

ORION

2,374
2
22
31

Does the whole thing (including the final SELECT *) take that long? You need to take that out and isolate it to verify that it is the INSERT not the fnial SELECT that is taking that long. – Nick.Mc Nov 08 '12 at 03:31

score 1 · Answer 3 · edited Nov 08 '12 at 06:01

There can be multiple reasons for this behaviour. Without execution plan we can only guess the reason, and can't be sure of the exact reason.

Since you are using DISTINCT ID in the query, I assume that ID is not the primary key. Can you modify your query to use PK in the WHERE clause.
It might be possible that the data in the table Y is too large, of the order of several millions, and due to lack if indexing(on the table) & Exists(in the query), query will take a long time.

Try this code just to check the speed

SELECT DISTINCT ID, ParentID
FROM Y WHERE ID IN (3,4)

SELECT statement that executes instantly, takes forever to insert into table variable

3 Answers3