CODEWITHSUNDEEP
sqlsql-server
dufaux
dufaux

Reputation: 893

Why is a sub query with In (select) faster than in ('xx','xx'..)

I have a query like this:

Select PATH
from FOLDER
where ...

This query return a list of path. (for example, 600 string).

When I use this list of path and do another request later with

Select *
From FOLDER
WHERE FOLDER.PATH IN ('path1','path2' [...])

it could take more than 30 seconds.

When I do this query:

Select *
From FOLDER
WHERE
FOLDER.PATH IN (Select PATH
    from FOLDER
    where 
    ...)

It take less than 1 second.

Is sql server perform the query and make Join ?

In this case, why on this post they advise to change the query with a JOIN?

Upvotes: 1

Views: 100

Answers (2)

sacse
sacse

Reputation: 3744

According to the query execution plan, IN (multiple values) has high 'selection cost', INNER JOIN and IN (select ..) work the same way (uses index scan) with low selection cost.

Thanks.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270653

I think the reason is pretty simple (and I'm not sure that the article mentioned in the comment addresses this).

SQL Server does a sequential search of an in list with constants. That means that for every value in the list that does not match, all values need to be compared. Some other databases optimize this by sorting the list and doing a binary search.

On the other hand, the in with a subquery can make use of an index -- which is essentially a binary search. That can be a significant reduction in computation, particularly for the values that are not in the list.

Upvotes: 1

Related Questions