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.
midrange.com
