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.

Answer 1

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

Answer 2

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.

Answer 3

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.

1. 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.

2. 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.

3. Try this code just to check the speed

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