Re: Embedded SQL Performance Issues
Randy Rasp <[email protected]> Wed, 20 Apr 2011 10:43:41 -0400 (EDT)
| Newsgroups | gmane.comp.systems.as400.jbausers |
|---|---|
| Message-ID | <[email protected]> |
I am very anxious to try Hudson's suggestion. Do I just create these logicals over PLP15? Do I need to change PL431 to use the new logical? PL431 currently uses PLP15 in the declare code. Will SQL figure out that it has a better access path and start using it? Thanks everyone! On Wed, Apr 20, 2011 at 9:38 AM, Prill, Steve wrote: > Hudson, > > Are the SQL monitors already on the system? How do you use them? > > Thanks---- > > Steve Prill | I.T. Manager | Rexair LLC |50 W. Big Beaver Rd. Suite > 350 | Troy, MI 48084 | 248-816-8627 | fax 248-524-2191 | > [email protected] > > > > -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf Of Hudson McVay > Sent: Wednesday, April 20, 2011 10:09 AM > To: 'System 21 Users' > Subject: Re: [SYSTEM21] Embedded SQL Performance Issues > > Does sound like an index problem. > I had the same issues a few years ago with 1/APY - report payments due > also, went from seconds to hours to run > I used one of the sql monitors to view what was being processed, it > suggested what views to create. > Created the views and that did fixed it. > > For the 1/APY issue I created 2 views, > > R PLR15 K LOGI15 K CONO15 K SUPN15 K > CURN15 K PDUE15 K ETYP15 K LREF15 > And > > R PLR15 K LOGI15 K CONO15 K > SUPN15 K RSTS15 > Worked for me, your milage may vary. > > Hudson McVay > > > -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf Of Randy Rasp > Sent: Wednesday, April 20, 2011 9:53 AM > To: System 21 Users > Cc: [email protected] > Subject: Re: [SYSTEM21] Embedded SQL Performance Issues > > Steve - > > Did you just upgrade your operating system to V6R1 by chance? > > We never had performance problems in the past, as our AS/400s in > recent years has been oversized. > > We upgraded to V6R1 two weekends ago and I am now getting sporadic > reports of jobs that appear to be stuck. When I investigate it's > always a RPG program with embedded SQL. The jobs are running, they > are just VERY slow. > > One job in particular is 1/APY - report payments due. It used to take > only 15-20 seconds to complete, and it now takes over 45 minutes. > When this job is running, our CPU jumps to 75+ % utilization. Prior > to V6R1, our AS/400 was rarely above 10% > > When this job is running, the program stack indicates that it's called > a program in QSYS called QSQROUTE. > > I signed onto Fix Central and we have all of the latest PTFs from IBM. > > I am not sure what to do at this point. > > Randy Rasp > AER Manufacturing > > > > On Wed, Apr 20, 2011 at 7:25 AM, Prill, Steve wrote: > >> 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 | >> [email protected] <mailto:[email protected]> >> >> >> _______________________________________________ >> This is the System 21 Users (SYSTEM21) mailing list >> To post a message email: [email protected] >> To subscribe, unsubscribe, or change list options, >> visit: http://lists.midrange.com/mailman/listinfo/system21 >> or email: [email protected] >> Before posting, please take a moment to review the archives >> at http://archive.midrange.com/system21. > _______________________________________________ > This is the System 21 Users (SYSTEM21) mailing list > To post a message email: [email protected] > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/mailman/listinfo/system21 > or email: [email protected] > Before posting, please take a moment to review the archives > at http://archive.midrange.com/system21. > > > _______________________________________________ > This is the System 21 Users (SYSTEM21) mailing list > To post a message email: [email protected] > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/mailman/listinfo/system21 > or email: [email protected] > Before posting, please take a moment to review the archives > at http://archive.midrange.com/system21. > > _______________________________________________ > This is the System 21 Users (SYSTEM21) mailing list > To post a message email: [email protected] > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/mailman/listinfo/system21 > or email: [email protected] > Before posting, please take a moment to review the archives > at http://archive.midrange.com/system21. _______________________________________________ This is the System 21 Users (SYSTEM21) mailing list To post a message email: [email protected] To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/system21 or email: [email protected] Before posting, please take a moment to review the archives at http://archive.midrange.com/system21.