CODEWITHSUNDEEP
sql-serverperformancenested
Mikeo
Mikeo

Reputation: 99

SQL Server Nested Query Performance

I need some help writing this query for SQL Server. The nested part makes this query take almost a minute to run on 27,000 records. I think it needs a temp table, but I have not done this before. Can someone give me an idea how I might do this?

SELECT  
r.ID,
r.CloseDate,
r.RepairOrderStatus 'Repair Status',
p.PartNumber ModInPN,
p.PartDescription ModInDesc,
pr.RMANumber,
c.FullName OpsTech,
    (SELECT COUNT (*)
    FROM dbo.TestPartsReplaced tpr
    WHERE tpr.RepairID = r.ID) Qty
FROM dbo.RepairTicket r LEFT JOIN dbo.Parts p ON r.ModuleInPartID = p.ID 
LEFT JOIN dbo.PartReturn pr ON r.PartReturnID = pr.ID
LEFT JOIN dbo.Contact c ON c.ID = r.ContactTechID

Upvotes: 1

Views: 76

Answers (1)

M.Ali
M.Ali

Reputation: 69594

Try this....

SELECT  
        r.ID,
        r.CloseDate,
        r.RepairOrderStatus 'Repair Status',
        p.PartNumber ModInPN,
        p.PartDescription ModInDesc,
        pr.RMANumber,
        c.FullName OpsTech,
        Qty.[Count] AS Quantity
FROM dbo.RepairTicket r LEFT JOIN dbo.Parts p ON r.ModuleInPartID = p.ID 
LEFT JOIN dbo.PartReturn pr ON r.PartReturnID = pr.ID
LEFT JOIN dbo.Contact c ON c.ID = r.ContactTechID
LEFT JOIN  (SELECT RepairID , COUNT (*) AS [Count]
            FROM dbo.TestPartsReplaced
            GROUP BY RepairID) Qty    ON  Qty.RepairID = r.ID

Upvotes: 2

Related Questions