Increase query performance where large number of values appear in primary key I have a query where there are numerous values passed in

This is a heavily simplified version of the query

SELECT Id, Name, &lt;other cols..&gt;
FROM Table
&lt;various joins etc.&gt;
WHERE Id in (1,2,3,4,5,6,7....1000,1001,1002 etc.)


I have noticed that when passing in fewer values the query is quick and uses the required index to return the data, however after a certain amount of values the query uses a constant scan and a hash table to join to the main result set which is killing the performance.

Is there a better way of returning this data?
Put your values into #tmp table and join it with the rest of your query