SQL Server : using IN clause is very slow

November 30, 2021

I’m creating a temporary table that is populated with the top 25 items for a specific user. I am then using this temporary table to get all the pieces for those items from another table.

These are my steps:

DECLARE @tableIds TABLE (id uniqueidentifier)

INSERT INTO @tableIds
    SELECT TOP 25 I.Id
    FROM Items I 
    INNER JOIN UserItems UI ON I.Id = UI.UserId
    WHERE UI.UserId = 'b846371d-5afc-eb11-b563-0003ff2a40c4'
      AND I.Valid = 1
    ORDER BY I.[TimeStamp] DESC

Now when I do a query on the items pieces using the IN clause on the id the query is very slow. Taking over 2 min to run and causing the app to timeout. There are only 1700 related rows in the item pieces table but there are over 2 million rows altogether. The query is like so:

SELECT 
    IPs.Id, 
    IPs.ItemId, 
    IPs.[Name], 
    IPs.PartNo, 
    IPs.PartPosition, 
    IPs.Size,
    IPs.Step
FROM
    ItemPieces IPs
WHERE 
    Ips.ItemId IN (SELECT id FROM @tableIds)

I thought about using and tried the EXISTS clause following numerous examples but I struggled to figure out how to get it work and return the related results. It was instead returning everything and not just the related rows.

How do I go about writing this so that I only get the related rows but it is fast as well?

Thanks

>Solution :

I would rework this to use an inner join to your limited list of values. Using your two queries it would be something like this.

SELECT IPs.Id, 
    IPs.ItemId, 
    IPs.[Name], 
    IPs.PartNo, 
    IPs.PartPosition, 
    IPs.Size,
    IPs.Step
FROM ItemPieces IPs
inner join 
(
    SELECT TOP 25 I.Id
    FROM Items I INNER JOIN
    UserItems UI on I.Id = UI.UserId
    WHERE UI.UserId = 'b846371d-5afc-eb11-b563-0003ff2a40c4'
    AND I.Valid = 1
    ORDER BY I.[TimeStamp] DESC 
) x on x.id = IPs.ItemId