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