RE: sql join performance issue

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

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.