I have a query where there are numerous values passed in
This is a heavily simplified version of the query
SELECT Id, Name, <other cols..> FROM Table <various joins etc.> 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?