Why does SQL Server perform a clustered scan when IN clause has a subquery?

Question

If I search for users like so:

SELECT *
FROM userprofile
WHERE userid IN (1, 2, 3)

The execution plan shows that UserProfile is using a Clustered Index Seek

If I change the IN clause to use a subquery:

SELECT *
FROM userprofile
WHERE userid IN (
    SELECT DISTINCT senders.UserId
    FROM messages m
    JOIN UserMessages recipients ON recipients.MessageId = m.MessageId
    JOIN UserMessages senders ON senders.MessageId = m.MessageId
    WHERE recipients.TypeId = 2
        AND recipients.UserId = 1
        AND senders.UserId <> 1
        AND senders.TypeId = 1 
)

The execution plan shows the subquery is using a Clustered Index Seek but the UserProfile outer query is using a Clustered Index Scan.

How can I write this so both inner and outer queries are using Seeks?

Answer 1

A set of seeks is only cheaper than a full scan if the rowcount is low. SQL Server is pretty conservative so if there is a chance many records will be found, it prefers to scan. In your example, it's pretty clear that userId in (1,2,3) will not return many rows. But with the subquery, SQL Server probably can't tell.

You can force a seek with:

from userprofile with (forceseek)

Answer 2

Forcing it to seek will not necessarily be any faster and it might be significantly slower.

if you rewrite your query to use EXISTS instead of IN, it may be faster:

select * from userprofile    -- << note that '*' is slow here
where EXISTS(
  select * from messages m   -- << but '*' is not slow here
  join UserMessages recipients on recipients.MessageId = m.MessageId
  join UserMessages senders on senders.MessageId = m.MessageId
  where recipients.TypeId = 2
    and recipients.UserId = 1 
    and senders.UserId <> 1 
    and senders.TypeId = 1
    and senders.UserId = userprofile.userid
)

It certainly won't be any slower.