We are registering/picking inventory with serial whether it is a purchase order, sales order, transfer order etc. These processes ran fine but after few days AX started getting into the NOT RESPONDING mode and never register/pick the inventory. We already have
- Re-indexed database from SQL and from AX SQL administration as well
- Ran Full compile application, Full CIL and Synchronization.
- Cleared server and client cache.
- Deleted the logs from InventSumLogTTS table.
- Check/ Synchronized whole database from AX.
- Ran Database consistency check from AX.
- Deleted batch job history with ‘Error’ status.
When I ran AX Trace parcer while registering inventory, a method named ‘existByInventDim’ in InventDimCombination was consuming alot of time.
select firstonly RecId from inventDimCombination
where inventDimCombination.ItemId == _itemId exists join inventDim
where inventDim.InventDimId == inventDimCombination.InventDimId
&& inventDim.ConfigId == _inventDim.ConfigId
&& inventDim.InventSizeId == _inventDim.InventSizeId
&& inventDim.InventColorId == _inventDim.InventColorId
&& inventDim.InventStyleId == _inventDim.InventStyleId;
This query was consuming alot of time, when I removed FIRSTONLY clause or change the join mode to INNER JOIN in the query, registering inventory process became much more faster but this doesn’t seem to be the solution because it is system’s code and it was running fine. What else I can do to resolve this performance issue.