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.