Sub Query Nightmare

[Oggog]

I have a query that takes about 30+ seconds to run in query analyzer. When I run it from .NET code the query times out. Even if I could get it to work from .NET, 30 seconds is way too long. I was hoping someone could help improve my query without losing the functionality of it.

[b]/*Begin Query*/ [/b]

Select Reqid, UserID, Attention, PurchasingStatus, RequestorID, DivID, Division.DivisionID, Requisition.BranchID as reqBranchID, Branch.BranchID, SecID, Section.SectionID, Status, FirstName, LastName, EmergencyOrder, Division.Description as divDesc, Branch.Description as branDesc, Section.Description as secDesc, awVendor 

FROM Requisition inner join Division on Requisition.DivID = Division.DivisionID 
    inner join Users on Requisition.RequestorID = Users.UserID 
    left outer join Branch on Requisition.BranchID = Branch.BranchID 
    left outer join Section on Section.SectionID = Requisition.SecID 

WHERE Status > 499 
and Status < 900 
and (PurchasingStatus = 'Purchased - Procard' or PurchasingStatus = 'Paid - Unencumbered') 

/* The following is killing my query performance */ 

and Reqid in 
     (Select Items.ReqID from Items where Items.ReqID = Requisition.Reqid 
          and (Items.Quantity > (Select Sum(ReceiveHistory.Quantity) 
               from ReceiveHistory where ReceiveHistory.ItemID = Items.ItemID) 
          or (Select Sum(ReceiveHistory.Quantity) from ReceiveHistory where ReceiveHistory.ItemID = Items.ItemID) is null)) 

/* The preceding is killing my query performance */ 

and LastName like '%Dall%' 

[b]/*End Query*/ [/b]

Now if I take out the part that is hurting the performance and run it alone, it runs fast. It's just that combining it with the rest of it is killing my query. It does return the correct results, it's just too slow to use. Any ideas?

 

[rac2]

Let us think about the pieces.

SELECT ItemID, SUM(Quantity) AS "QtyRcvd" FROM ReceiveHistory GROUP BY ItemID

That ought to yield the total number received for each ItemID.

Note that the use of SUM(Quantity) suggests that many rows in ReceiveHistory can have the same ItemID; on Monday we received 4 of ItemID 321, on Tuesday we received 5 more ItemID 321's, for a SUM of 9, but we requisitioned 12 so we hope to receive 3 more.

This first piece can be JOINed to Items to see how many of each item we have received and how many we hope to receive.

SELECT Items.ItemID, Items.Quantity, r.QtyRcvd
FROM Items
LEFT JOIN (

    SELECT ItemID, SUM(Quantity) AS "QtyRcvd"
    FROM ReceiveHistory
    GROUP BY ItemID

) r ON Items.ItemID = r.ItemID

"Just show me the items outstanding", you say.

SELECT Items.ReqID, Items.ItemID, Items.Quantity, r.QtyRcvd
FROM Items
LEFT JOIN (

    SELECT ItemID, SUM(Quantity) AS "QtyRcvd"
    FROM ReceiveHistory
    GROUP BY ItemID
    HAVING SUM(Quantity) < Items.Quantity

) r ON Items.ItemID = r.ItemID

This can be JOINed with the Requistion table. This query only has rows for incomplete items. Therefore it only has ReqID for incomplete requistions. A simple join acts like a filter, sometimes this is what you need.

SELECT ReqID, et cetera
FROM Requistion et cetera
JOIN (

SELECT Items.ReqID, Items.ItemID, Items.Quantity, r.QtyRcvd
FROM Items
LEFT JOIN (

    SELECT ItemID, SUM(Quantity) AS "QtyRcvd"
    FROM ReceiveHistory
    GROUP BY ItemID
    HAVING SUM(Quantity) < Items.Quantity

) r ON Items.ItemID = r.ItemID

) outstanding ON Requistion.ReqID = outstanding.ReqID
WHERE et cetera omitting the timeconsuming subqueries.

Well it is a theory anyway. Let me know if it works.