AW: Embedded SQL Logical file use

Hi,

using a logical file in a SQL-Statement will force the Query dispatcher to
reroute the execution of the select statement to the old (classic) Query
Engine. This rerouting may cost up to 10-15% of performance.

Instead of a logical file create a SQL view with the select and omit clauses
in the where clauses and use this one instead of the logical file. An SQL
view has no access path, but forces no rerouting. We could speed up a
program from 1.5 minutes to 20 seconds, by simply replacing a joined logical
file by an SQL view.

Try to execute your SQL statement with iSeries Navigator and analyze it with
Visual Explain. There you have an index advicer. May be it advices an
additional index.

Sometimes it also helps to rewrite the SQL-Statement and adding as much as
information as possible.

Otherwise we only can guess without seeing the SQL statement or knowing if
the SQL statement is executed with interactive SQL, or as static SQL or
dynamic SQL from a program. It's also important to know if the Open Data
Path is reusable or not.

In either way, there is no way to force the query optimizer to use the
access path specified in a logical file.

Mit freundlichen Gru?en / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Kyle Collie
Gesendet: Donnerstag, 30. Marz 2006 01:42
An: RPG List
Betreff: Embedded SQL Logical file use


We have an SQLRPG module that is opening a cursor based on a S/O
logical. Everytime the program is run the query optimizer is checking
for a better file to use and is eventually just coming back and using
the initial S/O logical that we specified in the select statement. Is
there a way to force the logical and prevent that processing time that
is wasted when it tries to find a better index?

The time it takes up is about 1 minute 20 seconds.

Thanks in advance,

--
This is the RPG programming on the AS400 / iSeries (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.