Re: sql join performance issue

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.