Hello, Michelle.

Sorry for the delay. I'm coming back from a long week-end here. Let's go back to this problem.

>I first learned to use BOs with the Web Connect framework, so my BOs are based on Rick's and mix business logic with data access. So some of them are more generic objects that handle a bunch of tables, and some are 1 BO = 1 table and are pretty strictly for data access.

Well, the problem here is that you're using too many BOs doing separated things that could be better handled as a single operation, IMHO.

>I made a bigger outline of what's going on to hopfully clarify what's going on. It's hard to condense a really complex process down to a few lines. :)

I know it is hard, but you did a good job at it.

First, I would time how much does it take to process a single record within the SCAN. I inserted a bit of code to do several times.

*##MS code
set textmerge on
set textmerge to TimeTable.txt additive
local lnStart, lnLoop

lnStart = seconds()
UI.Ship()
\UI.Ship - << lnStart - seconds() >>

  lnStart = seconds()
  ShippingBO.Save()
  \ShippingBO.Save() - << lnStart - seconds() >>

    * Generate list of all items in a cursor

    BEGIN TRANSACTION
    * Call the default to do the physical saving of the shipment info

    lnStart = seconds()
    PicklistBO.Save()
    \PicklistBO.Save() - << lnStart - seconds() >>

    lnStart = seconds()
    InventoryBO.Save()
    \InventoryBO.Save() - << lnStart - seconds() >>
      
      SELECT (CursorWithAllItemsToUpdate)
      lnLoop = 0
      SCAN
        lnLoop = lnLoop + 1
        lnStart = seconds()

        UpdateItemTable()
        UpdateItemQuantityTable()
        UpdateItemLocationTable()
        UpdateTransactionTable()
        UpdateDistributionTable()
        UpdateTotalsCursor()

        \SCAN Loop # <<lnLoop >> << lnStart - seconds() >>
      ENDSCAN

      * Update system table with totals
    END TRANSACTION

This is just an exercise. Of course, maybe you already know where the most time is spent, so you can skip this test.

>In each of the method calls in the scan, it uses a BO to access 1 table and update it with information from the current item that we're working on.
>
>The reason I can't do all my processing ahead of time is that if someone else edits a record before I get to it, it could change what I need to do. For example, if item ABC has 2 left in inventory, and I only need 1, it's a simple matter of subtracting. But if, when I do my processing, it says there is 2 left and when I get to it there really is 0 left, I need to go through an extra process and create an overship record. I won't know how many are there until I try to update the table, so I can't do it ahead of time.

For what you showed here, it seems that every BO is sharing the same DataSession. I don't like this very much, but we can try to use this in our own advantage. As some people told you on other threads, you can put Buffering to work for you.

If you have a test environment to make some trials, I'd try to do the following:
- Activate table buffering for any of the involved tables/views here.
- Let the BOs do their job (of course, they'll do it over the buffer, not the real data source)
- Issue all the TableUpdates in the end.

This can really boost the performance and avoid locking problems. Why? Because changes are performed first over the buffer (memory or local disk), and then, once the whole process finished, they are sent to the back-end right using primary keys and other fast-access mechanisms, and the "real" update will be done very quickly.

>I really appreciate you helping me with this. I know what a pain it can be to try and help someone with a complicated problem in a system you're not familiar with.

Don't worry. for any complex problem there is a simple, elegant... and wrong anwser. 8-)

Let's try a few things, and then go for the right solution instead <s>. I think the best approach is to do some refactoring over your BOs, but this would take longer. Anyway, if you want, I'll be glad to help with this. Basically, I think the whole process should involved probably just one BO representing this particular operation. It can need cooperation from a couple other BOs, but not as much as currently, and the whole transaction should be handled by a single BO.

Best luck,