fed: Excessive logging from inventory closing /recalculation

[Blog bot]

Источник: http://fedotenko.info/?p=300
==============

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.











Источник: http://fedotenko.info/?p=300