Re: Embedded SQL Performance Issues

Steve,

Problems with SQL performance are almost always down to there not being
a "suitable" index available for the SQL to use. In these cases, the
system either has to create an index (which can take a while) or process
all records (which can take a while). Occasionally, there would appear
to be a "suitable" index but for some reason the system chooses to
ignore it.

These sorts of problems can be solved in one of two ways:

1) change the SQL statement to match an existing index. This can be used
(so long as you have source) where it is possible to use an existing
index without changing the function of the program. A classic case would
be where these is an index keyed on Item Code that say omits status =
'X' but the programmer does not include the matching WHERE clause in the
SQL statement. The SQL parser would consider the logical not suitable as
it thinks you may want to see the 'X' records. If you don't, add the
WHERE Status<>'X' to the SQL and the system should use the logical file.


2) Create a new index to service the SQL. Look at the WHERE and ORDER BY
clauses in the SQL and create a logical to match.
This can be a better option as there is no need to change program code.
It is normally quite safe. (I say normally as there is a very unlikely
scenario where the new index will change (reverse) the FIFO sequence of
records and if a program is expecting a specific sequence it may not
function. Like I say, extremely unlikely but a possibility)

If the SQL statement uses multiple physical tables, it may be necessary
to create additional indexes on several of the tables. The system is
very good at advising on these. If you have the source, do the
following:
STRDBG UPDPROD(*YES)
STRSQL
copy the SQL statement into the SQL session and execute it.

The look at the low level messages in the Job Log. It will tell you what
access paths were considered, why certain ones were rejected , and which
one was used (if any). If none were deemed appropriate it may suggest
you create one.

We had a problem with Inventory month end that was getting longer to run
each month. Over a period of 6 months it went from 2 hours to 3.5 hours.
I did the trick described above and it suggested I create a logical over
INP95. I was very skeptical as the logical it suggested made absolutely
no sense. I did what I was told (computer knows best!) and the month end
now takes 11-12 minutes.

I have used the technique for about 16 years so probably someone knows
how to do it better using operations navigator which I rarely use.

Occasionally you will find performance changes from OS release to
another due to improvements in the SQL parser. I am on V5R4 but the fix
above worked fine on V5R3.

Best Regards
Gary.

Gary Lewis
IT Manager
Showerlux UK Ltd
[email protected]



-----Original Message-----
From: system21-bounces-Zwy7GipZuJhWk0Htik3J/[email protected]
[mailto:system21-bounces-Zwy7GipZuJhWk0Htik3J/[email protected]] On Behalf Of Prill, Steve
Sent: 20 April 2011 13:26
To: SYSTEM21-Zwy7GipZuJhWk0Htik3J/[email protected]
Subject: [SYSTEM21] 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-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 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.