Dynamics GP Item Stock Enquiry Window taking too long to display data

If it takes a long time for the Item Sock Enquiry window to display data after entering an item number, this is a sign of large amounts of data in GP slowing things down.

Stock Inquiry

In my example, entering an item number into the stock enquiry window, it then takes nine minutes to display the data. That is not a typo, I timed it with my iPhone…

stopwatch

Why is this so slow?

The document that helps us is The Balance in Item Stock Inquiry is incorrect in Microsoft Dynamics GP , this document under “more information” explains that IV30300 (Transaction Amounts History detail) is loaded for the item and iterated through  totalling up the values to give the balance. Running the following SQL gives us the items with the most transactions in that table, and the item I’m looking at has 9253 rows.

SELECT ITEMNMBR, COUNT(ITEMNMBR) 
FROM IV30300
GROUP BY ITEMNMBR
ORDER BY 2 DESC

So this is more data that expected. The “problem” is that there is too much data for the way GP works. The solution is to trim the historical transactions. Removing history from the IV module will reduce  the number of records the window has to iterate through and make the performance acceptable.

Comments (3) -

  • Hi Tim,
    Wouldn't adding an index to the table speed up the process?
    • Beat,

      Nah, the query takes 13ms to run, the SQL trace is showing other queries running quick (1-2ms). The problem is the scrolling window fill seems to be chunking to 25 record chunks and calling sub queries on each row, so all combined with DEX filling UI window with 9k rows, just gets very slow.

      Tim.
  • I concur, this window is slow, removing history is not ideal sometimes especially when the item is very high volume but 13 minutes, my slowest item opens in 3 minutes but have seen it up to 6!

    You hear people swearing sometimes when they forget and open this window when looking at our most popular items.

    Item enquiry, Item search and Item Stock Enquiry are top of my list for a replacement.

Add comment

Loading