Thursday, November 19, 2009

Dyn - Why is my inventory-related transaction posting so slow?

It is not uncommon for Microsoft Dynamics GP v9 and v10 users to report system sluggishness when posting inventory related transactions in Sales Order Processing, Purchase Order Processing or Inventory, especially if using the Average Perpetual valuation method.

The main reason why you may experience slow posting of inventory-related transactions is because Microsoft Dynamics GP uses an "inventory ripple" effect approach to recalculate the average cost of each inventory receipt layer due to inflow and outflow transactions in SOP, IV or POP. This is normal because in GP9.0 and GP10.0, the system is already observing the so-called "moving average" calculation. In addition, the system now "ripples" all item transactions. Hence, if transactions are backdated, each transaction would have to "ripple" through approximately n lines in the Inventory Purchase Receipts Work table (dbo.IV10200).

This new costing functionality in GP9 and GP10 also includes a new table, the Inventory Purchase Receipts Detail table (dbo.IV10201). To illustrate, if one of those lines in the Inventory Purchase Receipts Work table (dbo.IV10200) has been "sold", there should be a record of that sale in the IV10201 table. So now, we also have to "ripple" through the Inventory Purchase Receipts Detail table (dbo.IV10201) and each "layer" in that table, from the location of the "sale" record to the present. If you are a fairly high-volume transaction environment, this can take for ever!

A single transaction with a single line item, could very likely be updating quantity on hand and adjusted unit cost for over nth lines in the Inventory Purchase Receipts Work (dbo.IV10200) and an equal number in the Inventory Purchase Receipts Detail table (dbo.IV10201). This Microsoft Dynamics GP "inventory ripple" process may take considerable time to go through each receipt layer.

Average Perpetual and back-dated transactions seem to exacerbate the slowdown. But other know issues are also contributors, such as sold receipt layers not being removed during the Inventory Year-End Close (YEC) process.

These two issues could be the main causes why Microsoft Dynamics GP is slow in posting due to the "ripple effect" in inventory that they are producing.

Related Articles

To further explain the enhancements made to average cost calculation in GP 9.0 and above, please refer to KB 923960 - "Enhancements made to the calculation of average cost in Microsoft Dynamics GP".;en-us;923960

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

1 comment:

viCmaSS said...

Estimado Mariano, se me presenta el problema descrito en tu post, lei el KB en el cual como dices hay una explicacion del porque.
Existe alguna manera de mejorar la performace de la contabilizacion de ventas y recibos de articulos.
Manejo 2000 facturas de venta diarias cada una con un promedio de 5 lineas de articulos y 1 recibimiento de compras diario con 20 lineas.
La contabilizacion de un lote con 300 transcciones de ventas demora aproximandemente 30min.