|
midrange.com
Are you saying that if the physical file FILEA has 10,000,000 records, but
the logical FILEB of that physical has only 50,000 records due to the DDS
SELECT/OMIT, an SQL against the logical will STILL process 10,000,000 and
NOT 50,000?
Alan Shore
NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
<Matt.Haas@thomso
n.com>
Sent by: To
rpg400-l-bounces@ <rpg400-l@xxxxxxxxxxxx>
midrange.com cc
Subject
03/02/2006 10:13 RE: sqlrpgle select statements
AM
Please respond to
RPG programming
on the AS400 /
iSeries
<rpg400-l@midrang
e.com>
If you have select/omit criteria in the logical file, the query
optimizer will not consider it when determining the best access path.
When you include select/omit criteria in DDS based logical files, they
are both an index and a view at the same time which isn't normal in the
SQL world. The SQL book that explains the query optimizer is a very good
read if you are getting into SQL programming. Most things make sense
once you understand how it works but yes, there are times when you will
need to tweak the query a little bit (like adding flda=flda in the where
clause to get it to use the index with flda that it otherwise would
skip) to get it to use a better index but those types of tweaks are not
unique to the built in database.
Matt
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Thursday, March 02, 2006 9:04 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: sqlrpgle select statements
One reason that I would recommend in using a(n) LF in a SELECT/FROM, is
the SELECT/OMIT's within the DDS of the LF. This might be the reason
"why
in the world _some_ SELECTs will run in a fraction of the time when an
LF
is named instead of the PF"
Alan Shore
NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
qsrvbas@netscape.
net
Sent by:
To
rpg400-l-bounces@ rpg400-l@xxxxxxxxxxxx
midrange.com
cc
Subject
03/01/2006 08:26 RE: sqlrpgle select statements
PM
Please respond to
RPG programming
on the AS400 /
iSeries
<rpg400-l@midrang
e.com>
rpg400-l-request@xxxxxxxxxxxx wrote:
> 3. AW: sqlrpgle select statements (HauserSSS)
>
>You should NEVER use a logical file in an SELECT-Statement!
>The query dispatcher that decices which query engine will be used, will
>reroute all SQL-Statements that use a logical file to the Classic Query
>Engine (CQE). This rerouting will cost up to 10% of performance.
Although I won't recommend specifying the LF name in a SELECT/FROM, this
does bring up the question of why in the world _some_ SELECTs will run
in a
fraction of the time when an LF is named instead of the PF.
I would expect that the answer would be found within DB2 group PTFs, but
I
don't really know.
Tom Liotta
--
Tom Liotta
The PowerTech Group, Inc.
19426 68th Avenue South
Kent, WA 98032
Phone 253-872-7788 x313
Fax 253-872-7904
http://www.powertech.com
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
