Re: sql join performance issue

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.