Unknown execution time behavior of sql query

Question

case 1:

SELECT Column1, 
       Cloumn2, 
       Cloumn3 
  FROM TableName
 WHERE Column3 In (SELECT COL3 
                     FROM #TempTable)

Case 2:

DECLARE @valueClo3 varchar(50)
    set @valueClo3   = 'AnyValue'
SELECT Column1, 
       Cloumn2, 
       Cloumn3 
  FROM TableName
 WHERE Column3 In (@valueClo3)

Case 1 takes too much time(approx 3 min), while case 2 takes only 10 sec. #TempTable has only one value 'AnyValue'

Presumably different estimates of how many rows will match and different execution plans. — Martin Smith
– Martin Smith, Commented Feb 20, 2014 at 12:43
is this your complete query or you have posted simplified version of it ? highly unlikely to have such huge time difference for above posted cases . — Mudassir Hasan
– Mudassir Hasan, Commented Feb 20, 2014 at 12:48
Can you post the execution plan for each query? This should highlight where the difference is coming from. — GarethD
– GarethD, Commented Feb 20, 2014 at 12:52

SQLDoug · Accepted Answer · 2014-02-25 18:14:06Z

1

Subqueries take a much longer time than any other choice. That being said, IN will be faster, but still functions as an 'or'.

I would recommend trying to join your temporary table. Based on your example, an inner join will will likely accomplish your goal.

answered Feb 25, 2014 at 18:14

SQLDoug

513 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SQLDoug Over a year ago

To test, use SQL Profiler and issue a DBCC DROPCLEANBUFFERS before each one. Note both the CPU time and reads to determine which one will work best for you. Do remember, that your join column on your temp table may need indexed depending on it's size.

Narendra Jangir Over a year ago

I have improved performance of my query by applying index on the table. My index was in order column3, column2 and column1 which improves the performance.

Collectives™ on Stack Overflow

Unknown execution time behavior of sql query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related