Slow Performance when adding items on Sales Orders - related to really poor coding of spimIMSQtyAvailable

Question

Hi, 
 We have been having issues in SAGE2016 (we recently installed the May updates to see if it would help) when entering items on Sales Orders. 
 The last suggestion we got from support seems to help alleviate the problem. 
 The suggestion was to run 'recompile spimIMSQtyAvailable' on the SQL Server when we have the issue. 
 This gives us temporary relief until the plan in the procedure cache is bad again. 
 In looking at the stored procedure, it does a tremendous amount of looping with cursors that seems redundant. The stored procedure was written in 1998 and has had no updates for 20 years. 
 What is the right way to get this fixed so that it isn't a performance bottle neck? 
 
 This site ( https://www.rklesolutions.com/blog/sage-500-process-replenishment-orders ) has a great suggestion to change the overflow checks but we need this as base code from SAGE. 
 This SP was written in 1998 and needs to be rewritten with modern methods that are much more efficient.

.

jlputtman · Accepted Answer

Hi Marty, 
 We run into this challenge with some stored procedures in 500 that are native code. I figure that if you are at the point that you know the problem is with spimIMSQtyAvailable, that you may know some of this. Here is the process that I use when approaching the performance problem similar to what you are describing. 
 1. I review the execution plans in the plan cache for the stored proc in question. I like the free tool from SQL Sentry called Plan Explorer as it will show me the relative costs if each section of a proc as a percent, which helps me decifer where to focus. 
 2. If I can add indexes to help the offending areas of code then I do that as it doesn't change the code base. However with upgrades, those indexes can be dropped so I document the indexes that I add for repeatablity 
 3. if the code needs to be changed and it is native, I'll again document well what I am doing, test in a test environment. This is pretty safe as it won't get touched until the next upgrade. Again document. 
 4. I'll provide the code Sage with a recommendation to review and see if they will implement. Their challenge is the regression testing that they need to do in order to ensure that the change is safe in all circumstances. 
 Hope that helps, 
 John

Sage 500

Slow Performance when adding items on Sales Orders - related to really poor coding of spimIMSQtyAvailable

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING