Re: sql join performance issue

Are you using a QAQQINI with this job? By default, jobs will use a
QAQQINI file from QUSRSYS, unless you issue a CHGQRYA to cause your job
to use a specific version. If you have a QAQQINI in QUSRSYS, check to
see if it contains IGNORE_DERIVED_INDEX *YES.

See:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/r
zajq/qryopt.htm

If you do not have a QAQQINI in QUSRSYS, then you could CRTDUPOBJ from
the template in QSYS.
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/r
zajq/createqaqqini.htm

hth,
-Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
franz400@xxxxxxxxxxxx
Sent: Monday, August 30, 2010 3:11 PM
To: Midrange Systems Technical Discussion
Subject: RE: sql join performance issue

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.