RE: sql join performance issue

[<franz400@xxxxxxxxxxxx>]

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.