Re: sql join performance issue

Jim,

If you set IGNORE_DERIVED_INDEXES=*YES in QAQQINI, does the SQE use
the indexes?

The problem with the pre-v6.1 default of *NO, is that just the
existence of a derived index on a file will force the query down the
CQE path, even if the derived index in question doesn't apply to the
query.

Charles

On Mon, Aug 30, 2010 at 4:10 PM, <franz400@xxxxxxxxxxxx> wrote:

Ran with CQE (i should have checked it first) with reason "Derived Key or Select/Omit Index
There are a large nbr of select/omit indexes (30 or so,i've already slapped their hands) but the index does exist with no select omit of FILEA by order and FILEB by order - there is a "delete code" in FILEA which is what all the select/omits are about.
Jim

---- "DeLong wrote:

Are all tables referenced in your statement the actual PF objects?  Does
the needed index for FILEB use select/omit logic?  Does Visual Explain
say it's using SQE or CQE?

-Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
franz400@xxxxxxxxxxxx
Sent: Monday, August 30, 2010 1:33 PM
To: midrange-l@xxxxxxxxxxxx
Subject: sql join performance issue

Reviewing a performance issue, .net app opening a cursor that has sql
selects that are inserted into a temp table. Two large orders files
(FILEA & FILEB) and a temp file (FILEC) with the single record of the
Cust and Region selected.
FILEA inner join FILEB on aORDER = bORDER inner join FILEC on aCUST =
cCUST,aREGION = cREGION

Visual Explain is saying FILEB is a table scan even though it has an
index of ORDER (there is 1 rec per order in "a" and also "b").
Not understanding why it needed a table scan of a million recs to get 80
records for a cust/region

Jim Franz
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.