Excessive logging from inventory closing /recalculation

In my previous post, I stated that InventSumLogTTS table is not updated after related inventory transaction was physically posted. I was totally wrong.

In a while I had to make some performance tuning for a couple of installations, which suffered from bad inventory closing performance and overall conflicts between inventory closing and regular user work. After looking closer onto the problem, in both cases, I found out that the main reason for performance issues is a large set of updates to inventSumLogTTS table, coming from inventory closing/recalculation process. Since both installations use Weighted Average inventory model, routine inventory recalculation was causing some 2-3-5 millions of updates to InventTrans table and, respectively, creation of the same number of records in inventSumLogTTS table. The worst part of it is that InventSumLogTTS table is locked in pessimistic mode. It means, that if your users are running an order explosion update or trying to schedule production order, the system will start iterating over all InventSumLogTTS records for all items, which somehow participate in the explosion. Since now we have several millions of transactions, it will take some time. Since all explosion update process is run as a one big fat transaction, all updated records in inventSumLogTTS will remain locked till end of this transaction and all other users, trying to schedule another production order, which happens to share any of direct or indirect subcomponents with order being re-scheduled, will have to wait till the transaction ends. Also, most probably, part of the records related to scheduled items were inserted by inventory closing in not yet committed transaction(s). And every time when scheduling bumps into one of these items, it have to wait till transaction will commit. Shortly speaking, any attempt to run inventory closing/recalculation safely prevent most of production-related functionality.

Everything I said is related to DAX2009. In DAX2012, designers made some provisions to support inventory closing/recalculation execution in parallel with production operation.First of all – they enabled optimistic locking for InventSumLogTTS table. Second – they invented ‘Soft Deletion’ of inventSumLogTTS records, so the reqTrans update function can mark records as processed without causing long  locks to other processes (because of actual deletion). I have not checked this algorithm in practical usage and I am not sure that they addressed 100% of locking issues for inventSumLogTTS table, but I can believe that in version 2012 they addressed the most of them.

Yet, I am not sure that the very approach is correct. Let’s look into the following statements.

  1. Right now (at least – as of versions 4-2012R2) InventSumLogTTS is used only for dynamic update of net requirements (reqTrans). I do not know how it can be used in future, but now this table do not have any other use.
  2. MRP deals only with inventory transactions in, so to say, ‘Estimated’ statuses. After inventory transaction was registered (for receipts) or picked (for issues), from MRP point of view, it becomes a part of inventory-on-hand, one big net requirement which processed by MRP as a whole and it is based on inventSum data (not on inventory transactions).
  3. If an inventory transaction were physically updated (becomes a part of inventory-on-hand), then net requirements update logic apply this change ONLY on first change to physically updated status. If both new and old transaction statuses in a record of inventSumLogTTS are below or equal to Picked/Registered, then this record is not applied to net requirements and simply dropped during net requirement update process.
  4. Inventory closing/recalculation deal ONLY with inventory transactions who are well behind ‘Estimated’ status. It process transactions which were already Physically or Financially updated.
  5. Moreover, inventory closing/recalculation DO NOT change any of quantity/dimension/transaction status fields, which are somehow related to MRP. Well – I remember that average inventory recalculation/closing creates  dummy inventory transfers, but they are irrelevant for MRP.

So, I can simply draw a conclusion that any logging to inventSumLogTTS from inventory closing/recalculation should be simply disabled. The very fact that this was not made in a standard version of Dynamics AX can be considered as a design bug.

Now let’s come to more practical part. Dynamics AX do have API to disabling logging to InventSumLogTTS. You can call the following function: appl.inventUpdateOnhandGlobal().inventUpdateOnhand().parmSkipInventSumLogTTS() to enable or disable logging to inventSumLogTTS. I must mention, that in the end of transaction (commit or abbort), logging is re-enabled automatically, so in most cases you just need to disable logging and then do nothing.

Practically speaking – to disable logging to inventSumLogTTS during inventory closing/recalculation, you need insert a call to appl.inventUpdateOnhandGlobal().inventUpdateOnhand().parmSkipInventSumLogTTS(true); after every ttsbegin; statement (but not connection.ttsbegin() function), you find in the inventCostItemDim class. To disable logging for inventory closing/recalculation cancellation, you need to insert the same call (again – after every ttsbegin;  statement) into the class inventCostClosingCancel_WorkInvent. 

I made this change for two installations and I had quite positive results: Inventory recalculation/closing time dropped from days to hours (about 15-20 times) and all locking between production operations and inventory closing/recalculation have been gone.

Usual disclaimer: Use this thing with care, first try it in TEST environment, I am not sure about future compatibility and compatibility with your customizations and so on. Anyway – even in the worst case, InventSumLogTTS is cleared out on every MRP regeneration for dynamic plan, so It is not very big deal to skip this updates. In the worst case, just imagine that you ran unsuccessful session of MRP regeneration. 😉

 

 

 

 

Join the Conversation

8 Comments

  1. Hi Denis,

    Nice inventory closing knowledge sharing. actually, I have encounted long time inventory re-calculation on X2009 SP1.

    You mean is to insert a call to appl.inventUpdateOnhandGlobal().inventUpdateOnhand().parmSkipInventSumLogTTS(true); after every ttsbegin in inventCostItemDim,inventCostClosingCancel_WorkInvent 2 classes, and then inventory rep-calculation will not update InventSumLogTTS table?

    Please give me the confirmation.

    Great thanks!

    Andy

    1. Hello Andy

      Yes – you are totally right. You should place a call to the magic function after every ttsbegin in the classes, you mentioned and you won’t have any unnecessary logging from inventory closing and recalculation.

      Regards
      Denis

      1. Hi Denis,

        I have added the magic function in Class:InventCostItemDim, but I can not fond any ttsbegin in Class:inventCostClosingCancel_WorkInvent. could you give me moew help? Thanks!

        Andy

        1. Hello Andy
          Sorry for misleading you. In case of inventCostClosingCancel_WorkInvent, you should place the call to magic function just in the beginning of the method execute().

          A bit of background: ttsbegin/ttscommit statements are placed in the method of the superclass of our class inventCostClosingCancel.run(). This method starts transaction, calls methods init() and execute() and commits transaction. Since method init() does not do anything with inventory transactions, we can simply place the call to the beginning of the execute() method.

          Regards
          Denis

  2. Hi, excuse me if I resume this old post.. But I was wondering, on AX 2009, if you set the OccEnabled flag to YES in the InventSumLogTTS will not all the locks just go away?

    The worst thing that can happen if you do that is that you could encounter some sporadic update exception on the table, when two users edit the same record at the same time.

    Am I wrong?

    1. Hello Ludovico
      OccEnabled only influence behavior of select forupdate statement. If you update or insert data, touched records remain locked till end of transaction. So if you are running inventory closing in parallel from several threads, table records will still be locked from time to time, because one of the threads inserted a bunch of changes inventSumLogTts records. Also, in many cases, inserts cause more locks than update would do, because if update operation does not change key fields, it lock just updated record; Insert operation always change some key fields and it causes the whole page to be locked (or even several pages if insert caused a page split).
      Besides, it is not only about locking. In case of average costing model, every inventory closing inserts huge number of records in inventSumLogTTS (say – about 15-20 times of number of inventory transactions closed). These insertions can take more time then update of costing records itself and, according to my experience, disabling this logging decreases closing time by about 50%.
      Regards
      Denis

  3. Hi Denis,

    Thanks for the tip, we have used it in an AXAPTA version 4, and it has helped a lot.

    We have used your tip both regarding Inventory closing and Master planning.

    We also modified the Inventory closing a bit for better performance, created an index on the InventSettlement table, and reduced inventory closing time from 15 hours to less than 1 hour.

    And now both Master planning and inventory closing runs without blocking anything 🙂

    Best regards,
    Peter

Leave a comment

Your email address will not be published. Required fields are marked *