Re: sql join performance issue

[Alan Campin <alan0307d@xxxxxxxxx>]

If you use any logical file including one with select/omit criteria it will
always go to the CQE engine. Always use a physical file and let SQL figure
out what index it want to use. Just heard this from IBM and tested on our
6.1 system and it did go to the CQE engine if I used a logical.

You try running your query again running against physical files and see if
it gives the same results.


On Mon, Aug 30, 2010 at 2: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.