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
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.
Look at execution plan of the query; it might tell you more. Do you have up-to-date statistics? Does DBCC FREEPROCCACHE make any difference?