Sql-server – Prepared statements with many parameters is slow

sql serversql-server-2016

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

Best Answer

Looking at the (estimated) query plan, the only thing that sticks out as something that could cause an issue if estimates are incorrect is the residual predicate on c7.

NUTS

You would need to capture an actual plan to fully evaluate if the change is worthwhile, but it may be worth shifting that column from an included column to a key column.

Related Question