Re: 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: system21-bounces-Zwy7GipZuJhWk0Htik3J/[email protected] [mailto:system21-bounces-Zwy7GipZuJhWk0Htik3J/[email protected]] On Behalf Of Randy Rasp
Sent: Wednesday, April 20, 2011 9:53 AM
To: System 21 Users
Cc: SYSTEM21-Zwy7GipZuJhWk0Htik3J/[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 |
> sprill-hW6zm9MTFO98UrSeD/[email protected] <mailto:sprill-o28OYk0MVw58UrSeD/[email protected]>
>
>
> _______________________________________________
> This is the System 21 Users (SYSTEM21) mailing list
> To post a message email: SYSTEM21-Zwy7GipZuJhWk0Htik3J/[email protected]
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/system21
> or email: SYSTEM21-request-Zwy7GipZuJhWk0Htik3J/[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: SYSTEM21-Zwy7GipZuJhWk0Htik3J/[email protected]
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/system21
or email: SYSTEM21-request-Zwy7GipZuJhWk0Htik3J/[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: SYSTEM21-Zwy7GipZuJhWk0Htik3J/[email protected]
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/system21
or email: SYSTEM21-request-Zwy7GipZuJhWk0Htik3J/[email protected]
Before posting, please take a moment to review the archives
at http://archive.midrange.com/system21.