SQL Query works quickly with 19 items in "IN" clause - much slower with 20. Why?

Question

I have a query that includes this:

... AND Record.RecordID IN (1,2,3,10,11,12,13,16,17,18,26,27,28,557,31,32,33,36,37,93) AND ...

The problem seems to be that if there are 20 items or more in that list, the query takes over 25 seconds to execute. If there are less than 20, it executes immediately. Any ideas on how to optimize?

Answer 1

Place the RecordID's in a temporary table, and use an inner join to filter on them. For SQL Server, this looks like:

declare @RecordIds table (int RecordID)
insert into @RecordIds values (1)
insert into @RecordIds values (2)
...
insert into @RecordIds values (93)

select r.*
from Records r
inner join @RecordIds ri on ri.RecordID = r.RecordID