Re: system performance

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

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