Q:

SQL Server performance Issue. When ever the number of record "In parameter" increases, Query performance is degraded significantly

Select count (*) 
from table 
where id in (1,2,3,4,5,6.....500)

These ID's are populated externally through some script. As soon as In parameter exceeds a certain number of entries, the query slows down 6 times.
Any suggestion or help will be appreciated

A:

As has been suggested in the comments, you can use a temporary table to populate the externally generated ID's and then join on them. You can do create the temporary table as such :
Create table #TEMP(ID INT)
INSERT INTO #TEMP (ID) VALUES
(1), (2), (3), (4), (5) --Populate this with parameter as ID's are externally generated.

And then join as such :
Select          t.* 
from            table t
inner join      #Temp  temp on t.ID = temp.ID

I sincerely hope this is an example and you are not actually trying to do this :
Select count (*) 
from table 
where id in (1,2,3,4,5,6.....500)

Since if in your case, ID's are unique (which they mostly are) and are not being deleted (which is quite a usual practice), then, the total number of ID's in the IN clause will be the result of COUNT(*), in which case, you don't have to have an IN clause. You can just count the total number of values in the parameter you plan to use in the IN clause and that should be good.
Hope this helps!!!

