Re: sql join performance issue

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Also,

If you're still seeing the table scan, even after changing the
statement order and adding the indexes, you might try changing from
inner joins to left outer joins.  Assuming there's always a matching
record, changing to a left outer join won't change your results, but
may nudge the DB into using the indexes instead of a table scan.

At least it did for me, YMMV.

Even if one of my suggestions solves the problem, you may want to
consider contacting IBM anyway.

HTH,
Charles

On Mon, Aug 30, 2010 at 3:07 PM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
> My guess...it's seeing you statement like so:
> (FILEA inner join FILEB on aORDER = bORDER)
>   inner join FILEC on aCUST = cCUST,aREGION = cREGION
>
> Try this:
> FILEC  inner join FILEA on aCUST = cCUST,aREGION = cREGION
>  inner join FILEB on aORDER = bORDER
>
> You should have an index on FILEA with (CUST, REGION, ORDER)
> and an index on FILEB with (ORDER)
> and an index on FILEC with (CUST,REGION)
>
> Actually, you may try creating those indexes first, before changing
> your statement.
>
> With the right indexes, the DB should be smart enough to reorder your
> joins...but if it isn't and you think it should a call to IBM is your
> best option.
>
> hth,
> Charles
>
> On Mon, Aug 30, 2010 at 2:32 PM,  <franz400@xxxxxxxxxxxx> wrote:
> > 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.