Re: Embedded SQL Performance Issues

Randy,

Hudson has answered your question, but just to re-iterate:

Yes, you simply have to create a logical somewhere. That's it. No code change no recompile.
The SYSTEM decides what logical it will use, you have no control over this. There are good performance reason why you should always use the physical in the SQL statement (this is/was a JBA standard).

Regards
Gary.

-----Original Message-----
From: system21-bounces-Zwy7GipZuJhWk0Htik3J/[email protected] [mailto:system21-bounces-Zwy7GipZuJhWk0Htik3J/[email protected]] On Behalf Of Randy Rasp
Sent: 20 April 2011 15:44
To: System 21 Users
Subject: Re: [SYSTEM21] Embedded SQL Performance Issues

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 |
> sprill-hW6zm9MTFO98UrSeD/[email protected]
>
>
>
> -----Original Message-----
> From: system21-bounces-Zwy7GipZuJhWk0Htik3J/[email protected]
> [mailto:system21-bounces-Zwy7GipZuJhWk0Htik3J/[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: 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.
>
> _______________________________________________
> 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 e-mail and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom
they are addressed.
If you have received this e-mail in error please notify the
originator of the message.

The views and opinions specified in this email may well be those of
an individual and not truely reflect the opinion of Showerlux UK Ltd.
as a whole.

All possible care has been taken to ensure our network and email
service remains virus free. We can not however accept liability for
any virus transmitted to you from a Showerlux user. We urge you to scan any
emails with your own virus software for your own peace of mind.

Showerlux UK Ltd is a company registered in England and Wales, registration number 1102862.

Registered office: Sibree Road, Coventry CV3 4FD.

VAT No: 273439150.

This message has been scanned by MailControl
provided by www.cbs.cc/MailControl
_______________________________________________
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.