Hi,
in either way using DDS described logical files (neither with nor without
select/omit clauses) should not be specified in SQL statements.
Instead of logical files with select/omit clauses, create and use SQL views.
SQL statements that access logical files must be rerouted to the old less
performant CQE (classic query engine). The query optimizer first needs to
analyze the DDS described logical files and rewrites the SQL statement based
on the underlying physical files. After having rewritten the SQL statement
the optimization will start. At this time the optimizer does not know
anymore that there was a logical file specified, and checks and estimates
all existing indexes.
(... but to be honest, sometimes if there are a lot of access paths built
over the physical files and the logical files with Select/Omit Clauses are
rather complex, the optimizer may get a timeout and use the logical file
directly).
Bad performance may occur if the query optimizer cannot find an optimal
index and uses either a table scan (all rows including the deleted ones get
read) or even worse creates a temporary index for which several table scans
are necessary (i.e. determining the distinct keys, building the bitmaps). If
a temporary index will be built and the same SQL statement will be executed
in another job, the temporary index must also be built in the second job.
You need to analyze the SQL statements and look for missing indexes.
BTW beginning with release V5R4 all advised indexes are stored in a physical
file (SYSIXADV) located within the library QSYS2. In this table not only the
advised indexes are stored, but also how often they were advised and when
the last advice took place. The easiest way to analyze this proposed indexes
is to use the iSeries navigator, but it is also possible to access this
table with SQL or Query/400.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Lois Krake
Gesendet: Saturday, 29. August 2009 19:23
An: RPG programming on the IBM i / System i
Betreff: Re: system performance
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
