E1: 41F: PCW02 - Multiple Select Statements when adding to Item Structure Compromises Processing and Can Cause Web Exception

Last updated on SEPTEMBER 25, 2017

Applies to:

Symptoms

EnterpriseOne 9.2

When configuring a level in PCW02 (from PCW51) and attempting to add a Level Code, the system appears to be doing multiple unnecessary selects inside of Loop causing timeout/performance issues. With a significant amount of data, when configuring a level in PCW02 (from PCW51) and attempting to add a Level Code, the system returns a web exception indicating that "there was a problem with the server while running the business function Create_FCW02. Items are not being created properly in the FCW02 [Item Structure Definition]."

Review the logs and note that Create_Childs_FCW02 is doing the same SELECTs against the FCW02 over and over again inside of a WHILE loop. For example the following SELECT is executed 23 times.
SELECT  *  FROM PRODDTA.FCW02  WHERE  ( ISC9STRC = 'SKT' AND ISC9LVNO = 3.000000 AND ISC9LVCO = '01        ' AND ISC9PLNM = 'SKIRT-AP.COT/WHT' AND ISC9LVL0 = 'SKIRT-AP' )  ORDER BY ISC9STRC ASC,ISC9LVNO ASC,ISC9LVNM ASC. This causes a performance issue when the FCW02 has a large amount of data. The thought is that we only need to select the record once and either store the data in variables or a data pointer.

Changes

Cause