Re: system performance

Hi Lois

For the SQL-based technique, you can and should use the Visual Explain feature of Ops Nav - fantastic tool. You can start a monitor against a job or jobs and collect database performance data. Then analyze it with VE. Or you can open the SQL statement processor, enter any statement, including one from the monitor collection, and run them with VE. Then modify them to see if there is a difference - it is a different environment from where the stuff usually runs, so use it for local comparison only, but that can help with which is the better approach.

But what has brought you to look at these programs? Have you also looked at DASD? How much space is in use? What about pool allocation? Are you using expert cache?

You've received some very general recommendations - I hope one or two are on point for your situation.

Regards
Vern

Lois Krake wrote:

Vern:

SQL is being heavily used over the Select/Omit Logicals.

I have 1 programmer doing this & other programmers using SQL over physicals/logicals & handling the Select/Omit criteria in their programs. Not looking to hang anybody but I need to determine if there is a relationship between the poor system performance & the programming techniques.

Lois

----- Original Message ----- From: "Vern Hamberg" <vhamberg@xxxxxxxxxxx>
To: "RPG programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx>
Sent: Saturday, August 29, 2009 11:59 AM
Subject: Re: system performance


Jim

Not exactly - you can specify the S/O logical as the FROM file and the
query optimizer will let the classic engine run with it. But normally
you don't want to use an LF for the FROM file - and if there is an S/O
logical over the PF, then CQE is used, unless a directive is set in the
QAQQINI file. At V6R1 it defaults to *YES for IGNORE_DERIVED_INDEX,
which will let the new engine be used. SQE still won't use an S/O
logical, however.

If Lois is using SQL, she should also use Visual Explain in Ops Nav. But
it's not clear they are using SQL.

DYNSLT is documented with the following paragraph on performance -

"When your program does input operations to a logical file with the
DYNSLT keyword specified, all the records in the associated physical
file are tested by the system to see if they satisfy the select/omit
values. Only those records that satisfy the values are supplied to your
program. The testing of each record can result in slower I/O
performance, but can be more efficient than maintaining an access path
for the file. This is particularly likely for files read only
occasionally, especially when the physical files they are based on are
updated frequently. Using dynamic select/omit is probably also more
efficient for files with a high percentage of selected records."

This seems to say that with DYNSLT, you are doing a table scan when
doing a CHAIN or READE - maybe even for a SETLL. "...all the records in
the associated physical file are tested..." certainly sounds like a
table scan. On a very large file where you are returning only one
record, this would be costly for performance, seems to me.

Interesting that if you have S/O criteria on a non-keyed LF, you have to
use DYNSLT. This'd be like a WHERE clause without an index to back it up
- SQL has to create an index or do a table scan - both involve reading
the entire PF, IIRC.

So I think Lois needs to determine the kind of usage - if programs want
to work with most of the records in the PF, use DYNSLT - have to read
most of the file anyhow. If retrieving only 1 or 2 records, don't use
DYNSLT.

Also, DYNSLT enables access path sharing. If you're not sharing an
access path, hey, take the DYNSLT off - not benefit and could be a
detriment to have it. There is a thread on systeminetwork about it - one
post has the wrong reason for the problem, but it gets explained
eventually. There are other times DYNSLT is required - including join
LFs with criteria from more than one of the files being joined. The link
is http://forums.systeminetwork.com/isnetforums/showthread.php?t=56621

This is the kind of thing that would very likely benefit from using SQL
with sufficient index support. Worth a look, anyhow.

HTH
Vern

Jim Franz wrote:

If you are doing any SQL or Query, I believe the sql engines will not use an
index if it has select/omit. That can cause the sql engine to have to build
it's own index to use.
Jim Franz

----- Original Message ----- From: "Lois Krake" <lkrake@xxxxxxxx>
To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Sent: Saturday, August 29, 2009 9:36 AM
Subject: system performance

Hi:

I am trying to do research on the impact on system performance using
logicals with select-omit criteria(we do not use the DYNSLT keyword) vs.
using if statements in the rpg code. I don't know if there is a
performance difference but our system is slowing down & I am trying to
find reasons. I believe they are the direct result of poor programming
techniques & I am trying to identify them.

Lois
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.