Jiwa

Hi,
I have a client who's doing a few deletes of some debtors from Debtor Specific Pricing.
Using the Debtor/Debtor Class batch screen, it is taking ages - typically around 15 minutes but closer to 1 hour or more on several occasions. And it appears to lock up other users by putting on a SQL block.

During a SQL trace, I can see several SELECT statements which seem to be reading general ledger codes, and other SELECT statements which read and/or update IN_ProductAvailability.

1. Can you explain the relevance of doing all those selects? I would've though just the DELETE FROM IN_DebtorSpecificPrice WHERE RecID = <whatever> is sufficient? Or even better, DELETE FROM IN_DebtorSpecificPrice WHERE DebtorID = <whatever>.
2. Do you have any objection/reservation/comment if I just do a SQL query to get rid of those records from IN_DebtorSpecificPrice?
My SQL statement would be DELETE FROM IN_DebtorSpecificPrice WHERE DebtorID = '09876543210987654321'

Cheers

Danny

DannyC wrote:1. Can you explain the relevance of doing all those selects? I would've though just the DELETE FROM IN_DebtorSpecificPrice WHERE RecID = <whatever> is sufficient? Or even better, DELETE FROM IN_DebtorSpecificPrice WHERE DebtorID = <whatever>.

We use the inventory business logic to read each item, and remove the price, then we use the business logic to perform the save of the changes. This is why there would be seemingly unrelated queries.

This is a cut-down version of what happens when you update the prices:

Code: Select all

Do While SQLReader.Read = True
   Dim inventory As JiwaInventory.Inventory
   inventory = Manager.BusinessLogicFactory.CreateBusinessLogic(Of JiwaInventory.Inventory)(Nothing)
   inventory.Read(.Sanitise(SQLReader, "InventoryID"))
   ...
   Select Case UpdateMode
      ...
      Case UpdateModes.DeleteOnly
         For Each DebtorPrice As DebtorSpecificInventorySpecific In inventory.DebtorPrices
            If DebtorClassificationMode = DebtorClassificationModes.AllDebtors OrElse DebtorPrice.Debtor.DebtorID = Debtor.DebtorID Then
               If MatchesFilter(DebtorPrice.UseQuantityPriceBreak, DebtorPrice.QuantityPriceBreak, DebtorPrice.StartDate, DebtorPrice.EndDate) Then
                  inventory.DebtorPrices.Remove(DebtorPrice)
               End If
            End If
         Next
   End Select   
   ...
   inventory.Save
   ...
Loop

We do this because if you have business rules about what happens when a price is added, altered or modified, then by using the inventory business logic any plugins would be notified of the changes and business rules would be applied.

DannyC wrote:2. Do you have any objection/reservation/comment if I just do a SQL query to get rid of those records from IN_DebtorSpecificPrice?
My SQL statement would be DELETE FROM IN_DebtorSpecificPrice WHERE DebtorID = '09876543210987654321'

I don't see anything wrong with that.