1

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.

Matt
  • 25,943
  • 66
  • 198
  • 303

3 Answers3

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

onkar
  • 4,427
  • 10
  • 52
  • 89
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
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.

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
1

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)
    
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
NG.
  • 5,695
  • 2
  • 19
  • 30