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
CodePudding user response:
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