Improve Sage 500 Performance of Process Replenishment Orders

Improve Sage 500 Performance of Process Replenishment Orders by 5x to 10x on Larger Installs

Over the years we have had some of our larger customers ask us to help improve the performance of the Sage 500 Process Replenishment Orders task, specifically the process under Create Suggested Orders.

This blog post covers what we have found and a small improvement that makes a pretty big performance change when you are processing thousands of items.

Underlying this process is a stored procedure called spimCreateSuggOrd. This stored procedure has a few cursors, the main one that leads to a long processing time steps through each inventory item that is being analyzed. For each item, there is a call chain that calls spimLineItem, which then calls spimIMSQtyAvailable. This stored procedure is a great procedure that is used for multiple purposes within the Sage 500 environment. However, within the suggested order process, it is called to determine the quantity available for the item in a specific company and warehouse. And in this call, it checks 3 times for quantity overflows before setting the values to be returned to the calling procedure. The challenge is that these 4 queries in total, when the database has a large number of inventory bin records (timWhseBinInvt) will take a few seconds for each item, leading to minutes or hours of processing time.

We have found that putting in a try catch block around the population of the local variable and following with check for overflow will improve the performance of Create Suggested Orders by 5x to 10x on larger systems. At a customer, they were running longer than 10 hours for one of their runs. This change improved the run to between 1 and 2 hours.

Note that this change needs to be applied to the block with spimIMSQtyAvail that applies to the conditions where @_iCompanyID, @_iItemKey and @_iWhseKey are set and where @_iWhseBinKey, @_iInvtLotKey and @_iInvtSerialKey are null. This is at approximately line 800 in the procedure.

Have Questions or Need Help?