RE: sql join performance issue

Another option is to start with the file with the largest number of
records. So if FILEB has more records than FILEA, then start with FILEB:

FILEB inner join FILEA

With regards,
Carel Teijgeler

*********** REPLY SEPARATOR ***********

On 30-8-2010 at 20:10 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?

-----Original Message-----
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