RE: sql join performance issue

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