Re: sql join performance issue

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.