0

I receive an XML file that contains the updated details of the inventory and I need to process the xml to update the database.

Currently the routine looks like this:

    $SQL = "EXEC sp__ReviseInventoryStatus :ItemID,:Quantity,:Price";
    $rs= $DB->prepare($SQL,[PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]);

    foreach($xml->Item as $T)
    {
        $rs->bindParam(':ItemID', $T->ItemID, PDO::PARAM_STR);
        $rs->bindParam(':Quantity', $T->Quantity, PDO::PARAM_STR);
        $rs->bindParam(':Price', $T->Price, PDO::PARAM_STR);
        $rs->execute();
    }

where sp_ReviseInventoryStatus is a simple:

Update Items set Quantity=@Quantity, Price=@Price where ItemID=@ItemID

I created the required index in the table to increase speed, and everything seems working well.

But since items to update are often more than 150K and Items in the Items Table are more than 2M... it usually takes more than 30 minutes to complete the job..

Therefore I'm searching for a a better way to perform this task.

Can someone suggest a path?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Joe
  • 1,033
  • 1
  • 16
  • 39
  • 12ms/record sounds reasonable. The only way you're likely to get it any faster is by avoiding round trips sending the dataset in a single call via: 1. a Table Parameter (which I don't think PDO can do) or 2. an XML parameter (shredding it and inserting the extracted data inside the stored procedure). – AlwaysLearning Dec 02 '20 at 08:47
  • Hmm.. you mean passing directly the XML to DB and loop into the SP – Joe Dec 02 '20 at 09:13
  • If your XML only contains the Quanity, Price and ItemID values yes. But write it as a set-based operation, don't use loops or cursors. – AlwaysLearning Dec 02 '20 at 09:24

0 Answers0