Select where in () with many items \ IDs is timing out

Question

I have a node JS script which handles large amount of records and compare it to an existing records in database (SQL Server) The NodeJS dynamically construct the query. It fetches data from external source and compare it to my database. Example: I fetched 1000 records from external source and lets say all IDs are now in array called "ar". So I generate a query:

select from my_table where ID in ('--list of all IDs from ar--')

When it's 1k, 2k and up to 10k all works well. But when it starts increasing the queries starts to timeout.

So I apply some kind of paging mechanism I split the array to a fixed size and iterate it in chunks. meta code:

let total_results = 0
while i < ar.length {
let temp_result = execute_query("select from my_table where ID in ('--list of next 10K IDs from ar--')")
i += 5000
total_results += temp_result 
}

*the above is just meta code not real code.

So I do it in chunks and this way it's not failng.

My question:

Is there some other better and faster solution I can use in SQL Server?

Answer 1

The current query will parse at runtime the set of ids passed into the in clause. Large the number of ids, more would be the performance impact.

I would try by inserting the 10K ids this in a temp table first.(create an index on the id,if needed) and then query

select *
  from my_table 
 where id in (select id from temp_table)