Getting Index Scan instead Index Seeking if IN clause larger in Azure Sql

Question

This is the Non clustered index we have :

ALTER TABLE [Allocation].[allocation_plan_detail] ADD  CONSTRAINT [UQ_AP_TENANT_TYPE_STATUS_PLAN_ITEM_CLUB] UNIQUE NONCLUSTERED 
(
    [tenant_id] ASC,
    [item_type] ASC,
    [allocation_plan_status] ASC,
    [allocation_plan_type] ASC,
    [item_nbr] ASC,
    [club_nbr] ASC
)

Now when I am running this query :

SELECT *
FROM   Allocation.allocation_plan_detail
WHERE  tenant_id = 'sams_us'
       AND item_type = 'inseason'
       AND allocation_plan_status = 'draft'
       AND allocation_plan_type = 'continuous'
       AND item_nbr IN ( 10177, 107, 109, 112,
                         511993, 117, 120, 122, 31889 )

In the execution plan I am getting the perfect Index Seek. But when the item_nbr is large lets say aroun 200+ items its starting with constant scan and the query is extremely slow.

select * from Allocation.allocation_plan_detail where item_nbr in (72512,207317,...N(200+)) and allocation_plan_status='draft' and item_type ='inseason' and tenant_id='sams_us' and allocation_plan_type='continuous'` -> This is the issue.

Can anyone help me on this? How to resolve the issue? And is constant scan and table scan similar?

Is `select *` actually required? How many other columns are in your table? How many columns aren't referenced or included in your index? Based on your statement `item_nbr` seems important - and probably has the highest cardinality - yet it's the fifth column in the index key, not the first. — AlwaysLearning, Jan 17 '23 at 10:10
Yes we need to use *. And yes item_nbr is one of the most important keys along with tenantId. But on increasing elements in IN clause why it choosing constant scan? Isn't it weird? — SUVAM ROY, Jan 17 '23 at 10:15
A **constant** scan is normal if you have a larger number of in values than can be expressed as seek predicates on an index seek execution plan operator (IIRC >= 64) - this isn't itself a problem as it can still drive a nested loops with an index seek on the inside. You seem to be conflating constant scan and index scan. What does the XML for the actual execution plan show? You can paste it at https://www.brentozar.com/pastetheplan/ — Martin Smith, Jan 17 '23 at 10:29
@MartinSmith Actually the IN query is extremely slow like its taking 5-6 min for 20million data and 200 elements IN clause. This something is not expected... — SUVAM ROY, Jan 17 '23 at 10:33
you need to show the execution plan. Just telling us that it contains a constant scan isn't enough as this in itself isn't the problem. Maybe it then doesn't do nested loops + seeks after that but we can't see that — Martin Smith, Jan 17 '23 at 10:34
@MartinSmith in the editor it saying "The supplied XML did not parse correctly. Are you sure you have a valid query plan XML text?" .... Is there any other way to share the plan with you? — SUVAM ROY, Jan 17 '23 at 10:59
Ah I remember that the site doesn't recognize some versions and it just needs a manual edit to one of the properties in the XML but I can't remember the exact change that needs to be made. You can use pastebin or similar? — Martin Smith, Jan 17 '23 at 11:02
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/251207/discussion-between-suvam-roy-and-martin-smith). — SUVAM ROY, Jan 17 '23 at 11:15
Can you please modify my query in your answer ? It will be great help. — SUVAM ROY, Jan 17 '23 at 11:25

Martin Smith · Accepted Answer · 2023-01-17T13:52:05.743

The constant scan is not itself indicative of a problem.

This is normal if there are >= 64 predicates in an IN list as this is the maximum number of seek operations that can be applied to a seek operator directly in an execution plan.

It is still perfectly possible for rows to come out of the constant scan, go into a nested loops and be used to perform an index seek using your UQ_AP_TENANT_TYPE_STATUS_PLAN_ITEM_CLUB index.

This is not the plan you get however. You get the following (I've highlighted elements of the operator tooltips/properties that I refer to later - from viewing the execution plan in SSMS)

Though when you add the WITH (FORCESEEK) hint you do get the earlier described plan

The reason why the execution plan is not chosen anyway is because your index does not cover all the many columns returned by this query so additional key lookups will be needed. SQL Server costs the plan with the scan of the covering index and hash join as 738.443 units vs 1195.91 units when you force the seek.

In this case SQL Server estimates that 462,724 rows will be returned and only 58,470 actually are so it over estimates the number of lookups that will be needed and corresponding costs of that option however.

If you do use the WITH (FORCESEEK) hint in production it does have a risk that for a different IN list you may end up encountering an eye watering number of lookups and it ends up making things significantly worse than the hash join plus covering index scan plan.

You may even be better breaking the query up into two stages. One that gets the clustered index keys of the rows to be returned into a temp table or table variable and then a semi join against that. This would allow other plans than just N lookups to be considered.

DECLARE @allocation_plan_detail_ids TABLE(allocation_plan_detail_id INT PRIMARY KEY);

INSERT @allocation_plan_detail_ids(allocation_plan_detail_id)
SELECT 
        allocation_plan_detail_id /*Implicitly Included in the index as CI key*/
from Allocation.allocation_plan_detail 
WHERE allocation_plan_status='draft' and item_type ='inseason' and tenant_id='sams_us' and allocation_plan_type='continuous' and item_nbr in (...);


select * 
from Allocation.allocation_plan_detail 
WHERE allocation_plan_detail_id IN 
(SELECT a.allocation_plan_detail_id FROM @allocation_plan_detail_ids a)
OPTION (RECOMPILE) /*So cardinality of result taken into account*/

In your case when you did hint the FORCESEEK the "real" execution time was actually significantly shorter (434 ms vs 18.123 seconds) - but unfortunately for some reason it spent a lot longer on ASYNC_NETWORK_IO waits (sending the results to the client and waiting for the client to process them).

This wait time by far dominated the overall elapsed time. As both queries are returning exactly the same results this seems like a transient issue assuming you are using exactly the same client in both cases.

One way to take the ASYNC_NETWORK_IO out of equation during development would be to simply SELECT ... INTO a temp table rather than do the final SELECT so nothing is sent to the client at all. Of course you need to remember to remove this when finished comparing the options so you do actually return the required results.

I tried as you shared. But I havent seen any performance improvement. And I have shared the new plan in the chat. BTW are you suggesting to put all columns into Nonclustered index which are not used in filter as well ? — SUVAM ROY, Jan 17 '23 at 11:43
That is a big performance improvement. 0.434 seconds vs 18.123 seconds — Martin Smith, Jan 17 '23 at 11:45
And including those columns in the index would get rid of the lookups and reliably end up with a seek. I haven't suggested you do that though. Looks like the query returns about 50 columns so that would be a lot of included columns. You'd need to evaluate the effect of this on your overall workload — Martin Smith, Jan 17 '23 at 11:48
Ah the reason why you don't see any perf improvement is because even when it is sped up on the SQL side it just ends up spending time waiting on `ASYNC_NETWORK_IO` sending the results to your machine — Martin Smith, Jan 17 '23 at 11:50
So in such case what is the most secured but most efficient way of working? And how to see the performance improvement in my machine? — SUVAM ROY, Jan 17 '23 at 11:51
To test performance of the query itself and remove overhead of sending rows to the client and waiting on the client to process them you can change the query to do a `SELECT ...INTO` a temp table - this keeps all the rows on the server so can allow you to more easily compare without this potential additional variability. If you ran both queries on the same machine maybe your network connection was just a lot busier on the second one as both queries return the same results but the second one had 149 seconds of `ASYNC_NETWORK_IO` waits — Martin Smith, Jan 17 '23 at 12:00
Like this ? with cte as( select * from Allocation.allocation_plan_detail with(forceseek) where allocation_plan_status='draft' and item_type ='inseason' and tenant_id='sams_us' and allocation_plan_type='continuous' and item_nbr in (2..N) ) select * from cte; — SUVAM ROY, Jan 17 '23 at 12:05
No. A CTE is not a temp table and this still sends all the results to the client which seems to cause huge amounts of variability in your case. I'm talking about a method where *during development* you can test changes to the execution plan and remove that variability. — Martin Smith, Jan 17 '23 at 12:06
I know I am asking too much but can you please give an example. Thank you so much for all your time and assistance. It would be great if you provide the last example as well. — SUVAM ROY, Jan 17 '23 at 12:09

Getting Index Scan instead Index Seeking if IN clause larger in Azure Sql

1 Answers1

Linked