Tabular parameters for a sentence in a section are slower than simple ones in an article

Question

I have sql queries with some IN clauses. To improve query plan caching, I decided to use table parameters. Here is an example of WHERE ID IN (SELECT ID FROM @P1) . @ P1 is a 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 are getting slower. Request example:

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

which runs in 2.1 seconds on my db.

And the old request:

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

performed in 1.8 seconds.

I notice a difference in terms of requests. The plan for the first request consists of two parts (one for zero verification, one for the proposal), and then concatenation. Although the background is just an index search.

Is there a way to improve my parameterized query to execute faster?

PS This is just a sample of a distilled request, I want to know that something is wrong with this part in (select id from @p1) .

Answer 1

A few suggestions:

• Do not use SELECT * - just list the columns you need .
• Always use the schema prefix .

• Use EXISTS , not IN (since the first one may close):

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

• The above may still contain concatenation (which essentially means UNION ), but you can try writing your own UNION ALL to avoid 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; 

My concern is that any of your existing options take almost two seconds. Make sure that SomeTable.SomeForeignId has an index, not just the foreign key constraint, but the actual non-clustered index. It is not clear in your question that this is the index you are looking for.