CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Zath
Zath

Reputation: 557

TSQL Update table timing out

The sql in the stored procedure has several updates. I have fixed one and the one left is still timing out.

The table being updated has over 600000 records. The select statement in the sql runs fast when tested alone so it has to be the update part.

UPDATE tblPackage  
SET IsUpdated = 1,
    ShippingCost = (
        SELECT SUM(SC.ShippingCost)
        FROM tblShippingCost SC
        WHERE SC.PackageID = P.PackageID)
FROM tblShippingCost SC
    INNER JOIN tblPackage P ON P.PackageID = SC.PackageID
    INNER JOIN tblRunPackage RP ON RP.PackageID = P.PackageID
    INNER JOIN tblRun R ON R.RunID = RP.RunID
    INNER JOIN tblOrderItem OI ON OI.OrderItemID = R.OrderItemID
    INNER JOIN tblOrder O ON O.OrderID = OI.OrderID
WHERE 
    O.InvoiceID IS NULL 
    AND SC.PackageID = P.PackageID

I welcome any and all suggestions.

Thanks!

Upvotes: 0

Views: 106

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44326

You have joined tblshippingcost an extra time, this will not give the wrong result. But it will take more time joining and calculating. Try removing that part from your update like this:

UPDATE tblPackage  
SET
  IsUpdated = 1,
  ShippingCost = (
       SELECT SUM(SC.ShippingCost)
       FROM tblShippingCost SC
       WHERE SC.PackageID = P.PackageID
     )
FROM 
    tblPackage P
INNER JOIN 
  tblRunPackage RP ON RP.PackageID = P.PackageID
INNER JOIN
  tblRun R ON R.RunID = RP.RunID
INNER JOIN 
  tblOrderItem OI ON OI.OrderItemID = R.OrderItemID
INNER JOIN 
  tblOrder O ON O.OrderID = OI.OrderID
WHERE 
    O.InvoiceID IS NULL

Upvotes: 1

ThePravinDeshmukh
ThePravinDeshmukh

Reputation: 1913

try this 

UPDATE tblPackage  
SET IsUpdated = 1,
    ShippingCost = SUM(SC.ShippingCost)
FROM tblShippingCost SC
    INNER JOIN tblPackage P ON P.PackageID = SC.PackageID
    INNER JOIN tblRunPackage RP ON RP.PackageID = P.PackageID
    INNER JOIN tblRun R ON R.RunID = RP.RunID
    INNER JOIN tblOrderItem OI ON OI.OrderItemID = R.OrderItemID
    INNER JOIN tblOrder O ON O.OrderID = OI.OrderID
WHERE 
    O.InvoiceID IS NULL 
    AND SC.PackageID = P.PackageID

Upvotes: 0

Related Questions