CODEWITHSUNDEEP
sqlsql-server-2005execution-time
Narendra Jangir
Narendra Jangir

Reputation: 241

Unknown execution time behavior of sql query

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'

Upvotes: 1

Views: 95

Answers (1)

SQLDoug
SQLDoug

Reputation: 51

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.

Upvotes: 1

Related Questions