CODEWITHSUNDEEP
sql-serverinserttable-variable
Matt
Matt

Reputation: 26971

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

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.

Upvotes: 1

Views: 2255

Answers (3)

unitario
unitario

Reputation: 6535

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

Upvotes: 2

NG.
NG.

Reputation: 6043

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)

Upvotes: 1

ORION
ORION

Reputation: 2401

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.

Upvotes: 1

Related Questions