Prepared statements with many parameters is slow I have an application written in PHP with Laravel that regularly prepares and executes statements like this:

   -- All parameters are varchar(10)
   SELECT c1, c2, c3, c4
      from MyBigTable
     where is_active = 1
       and c1 in (@P1, @P2, @P3, @P4 ... @P250)
       AND c2 is not NULL


Users have a big data grid, and they can select many rows (there is even a button to select 'ALL'). If they select 250 rows, this statement is what happens. But it takes more than one minute to run, which is unacceptable.

Table MyBigTable has about 10 millions rows. The estimated execution plan shows that 100% of time is spent on an "index seek, non-clustered". From this I deduce that the situation can not be improved using indexes, and that the only issue is in the use of prepared statements. (If you think I am wrong, just let me know). Moreover, I understand that these prepared statements are prepared, used once and discarded, so I don't think they are really beneficial.

What recommendation should I give to the developers?

Should I just tell them to stop using prepared statements, and hardcode the 250 values in the query?

Or should I give them some workaround, like the use of temporary tables (make a temporary table, insert 250 values, then make a query on MyBigTable JOINed with temp)?

Or any other idea?

EDIT: execution plan https://www.brentozar.com/pastetheplan/?id=rJ-b2XalH
1) IN clauses are during the optimization phase expanded to multiple OR. Example: C1 = @P1 Or C1 = @P2.

2) If the parameter count is greater than 63 the optimization process will build an internal table for it. Having said that, I’m not so sure about the stats being generated on the dynamically generated temp table.

3) Putting your parameters in a #temp table may result in a slightly different execution plan, however, this may also introduce concurrency issues depending on the design of your application.

4) I would, before introducing a temp table, try to rewrite the query into smaller logical chunks. For example, isolate the filter process against an optimized smaller outer table and cross apply that with the inner table. 

5) Ensure that your stats are updated and that you are getting a parallel execution plan. Would be nice if you could share your plan so we can have a peek at what’s going on.


  Blockquote
  The estimated execution plan shows that 100% of time is spent on an "index seek, non-clustered". From this I deduce that the situation can not be improved using indexes, 


6) "index seek" can be "bad" if they represent a lot of logical reads. You should check the statistics in order to be sure that index tuning won't yield any better results.