Embedded SQL Performance Issues

We are at 3.5.2 SP3. A user complained to me about the performance when
the quantities were changed on a Bill of Material via menu MDM option 21
(Routes/Structures). This has been occurring forever but we are just
now hearing about it. It was taking about 20 seconds to change the
quantity on one part but when they would change two of more quantities
then press F8 to update it could take 3 or 4 minutes to complete the
update. I had a vendor look at the application and they found an issue
with the embedded SQL. They found and fixed the issue in program DB590
and the update is now nearly instantaneous. In this case they had to
create a new logical and modify the SQL.



Today another user complained about the performance, that has been
occurring forever, when using the "Routes/Structures Audit" (MDE option
8). After a part/route is entered and a change is selected via "2=Route
Structure Inquiry" and enter is pressed on the next screen, it can take
up to 5 minutes to get to the screen titled "Process Route Audit Inquiry
- Inputs". The issue appears to be with various SQL statements in
program DB561.



There seems to be a pattern here and I know there are many other SQL
programs in System 21 and I now wonder if this performance issue is
occurring in other programs but our users have just learned to live with
it. In the 8/MDE problem above the users have simply chosen not to use
the option.



Has anyone else experienced similar issues with embedded SQL and maybe
found a fix? Unfortunately, we do not know how to troubleshoot embedded
SQL issues. I know my vendor can look into it and fix it but wonder if
there is a general fix for these type of issues with embedded SQL that
my programmers can look into.



Thanks in advance for your input.



Steve Prill



Steve Prill | I.T. Manager | Rexair LLC |50 W. Big Beaver Rd. Suite 350
| Troy, MI 48084 | 248-816-8627 | fax 248-524-2191 |
sprill@xxxxxxxxxxxxx <mailto:sprill@xxxxxxxxxxxxx>