CODEWITHSUNDEEP
sqlsql-server
SouthL
SouthL

Reputation: 175

Why is an IN statement with a list of items faster than an IN statement with a subquery?

I'm having the following situation:

I've got a quite complex view from which I've to select a couple of records.

SELECT * FROM VW_Test INNER JOIN TBL_Test ON VW_Test.id = TBL_Test.id 
WHERE VW_Test.id IN (1000,1001,1002,1003,1004,[etc])

This returns a result practically instantly (currently with 25 items in that IN statement). However when I use the following query it slows down really fast.

SELECT * FROM VW_Test INNER JOIN TBL_Test ON VW_Test.id = TBL_Test.id 
WHERE VW_Test.id IN (SELECT id FROM TBL_Test)

With 25 records in the TBL_Test this query takes about 5 seconds. I've got an index on that id in the TBL_Test.

Anyone got an idea why this happens and how to get performance up?

EDIT: I forgot to mention that this subquery 

SELECT id FROM TBL_Test

returns a result instantly as well.

Upvotes: 2

Views: 244

Answers (1)

Frank Allenby
Frank Allenby

Reputation: 4392

Well, when using a subquery the database engine will first have to generate the results for the subquery before it can do anything else, which takes time. If you have a predefined list, this will not need to happen and the engine can simply use those values 'as is'. At least, this is how I understand it.

How to improve performance: do away with the subquery. I don't think you even need the IN clause in this case. The INNER JOIN should suffice.

Upvotes: 1

Related Questions