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.

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.

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).

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.

Inventory closing/recalculation deal ONLY with inventory transactions who are well behind ‘Estimated’ status. It process transactions which were already Physically or Financially updated.

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. 😉

Most of Dynamics AX specialists (especially from support) heard about inventSumLogTTS table. Everyone knows that this table somehow is related to MRP; Everyone knows that if you do not run MRP, your inventSumLogTTS table grows bigger and bigger and eventually you need to clean it somehow. But since detailed documentation on static/dynamic plan update algorithms is absent, not many people understand how Axapta actually use this table for plan updates; what are advantages and caveats of dynamic vs static plan use;how order explosion differs from regeneration planning and so on…

I wrote this text mostly from my experience with Dynamics AX 2009. In the new version (DAX2012) approach remained the same from functional point of view, only technical details changed, so this information can be useful also for people using DAX2012.

A bit of history

As I wrote in my blog before, inventSumLogTTS table began its life (somewhere in late releases Axapta 2.5) only as a tool to track a list of changes which was applied to inventSum table in independent DB transaction. Should the main transaction be rolled back, the system was to iterate over related records in inventSumLogTTS and programmatically rollback changes to inventSum. Starting from Axapta 3.0, inventSumLogTTS table started to be also used to support dynamic update to MRP plan. In version Dynamics AX 4.0, the whole Inventory MultiTransaction System was dropped from Axapta and replaced with a new approach. Nevertheless inventSumLogTTS table still exists in the system, but nowadays it is used only to support dynamic MRP plan update. Today, name of the table is misleading, because now it is not used to log InventSum updates. As of versions 4-2012, it is used only to keep track of inventTrans changes, which have not been applied to dynamic plan yet.