Table-valued parameters for in clause are slower then simple in clause

Question

I have sql queries with some IN clauses. In order to improve query plan caching I decided to use table-valued parameters. Here is the sample WHERE ID IN (SELECT ID FROM @P1). @P1 is variable of the following type :

CREATE TYPE [dbo].[Ids] AS TABLE(
    [ID] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)

But I notice that some queries become slower. Here is query example :

select * from SomeTable s where ((s.SomeForeignId in (select id from @p1)) or s.SomeForeignId is null)

which performs in 2.1 sec on my db.

And old query :

select * from SomeTable s where ((s.SomeForeignId in (1,2,3.....)) or s.SomeForeignId is null)

performs in 1.8 sec.

I notice the difference in query plan. Plan of the first query consists of 2 parts (one for null check, one for in clause) and then comes concatenation. While the second plan is just index seek.

Is there any way I can improve my parameterized query to perform faster?

P.S. This is just the sample distilled query, I want to know if anything is incorrect with this in (select id from @p1) part.

Answer 1

A couple of suggestions:

1. Don't use SELECT * - just list the columns you actually need.

2. Use the schema prefix always.

3. Use EXISTS rather than IN (since the former can short circuit):

   SELECT cols FROM dbo.SomeTable AS s 
     WHERE EXISTS (SELECT 1 FROM @p1 WHERE ID = s.SomeForeignId)
     OR SomeForeignId IS NULL;
   

4. The above may still end up with a concatenation (which essentially means a UNION), but you may try writing your own UNION ALL to avoid the OR:

   SELECT cols FROM dbo.SomeTable AS s 
     WHERE EXISTS (SELECT 1 FROM @p1 WHERE ID = s.SomeForeignId)
   UNION ALL
   SELECT cols FROM dbo.SomeTable
     WHERE SomeForeignId IS NULL;
   

What's troubling me is that either of your existing variations take almost two seconds. Please be sure that there is an index on SomeTable.SomeForeignId - not just a foreign key constraint, but an actual non-clustered index. It's not clear in your question that this is the index you get a seek on.