1

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

4

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.

| improve this answer | |
4

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.

| improve this answer | |
1

It would be useful to see the actual query plan for one of the larger instances, and perhaps the extra details emitted when you run it with SET STATISTICS IO ON. I suspect it may be scanning the whole table at that point, or this whole index large index, as I've seen similar behaviour in the past with large static IN clauses. This is where "index skip scanning" as implemented by Oracle could be useful, but SQL Server does not support that. As you are constructing the SQL prepared statement in code anyway, you could try many UNIONs to emulate the behaviour:

   SELECT [c2], [c1], [c5], [c4], [c3], [c6]
     FROM [MyBigTable]
    WHERE [c7] = 1
      AND [c3] IN = 'a'
      AND [c4] IS NOT NULL
UNION ALL
   SELECT [c2], [c1], [c5], [c4], [c3], [c6]
     FROM [MyBigTable]
    WHERE [c7] = 1
      AND [c3] IN = 'b'
      AND [c4] IS NOT NULL
UNION ALL
      ...

(using ALL with UNION to avoid an unnecessary distinct sort that could be expensive)

Whether this is any better or not massively depends on the number of rows each SELECT returns. On the number fo rows...

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.

How many rows does this imply are being considered? If selecting "all" means "look at all those 10s of millions of rows" (or "look at most of them") then it might simply be this is fast as your IO subsystem can do the job, even with a skip-scan on an ideal index. And you might have an application design issue rather than a database one: is the "all" option actually of any real use to the user at this point?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.