Outer-Join in Query very slow

Aigy

New Member
i have a question concerning outer-joins. maybe it was asked before but i couldn't find it (sorry for that inconvenience).

a have a query:

query q:query-prepare(
"for each company,
first p left outer-join where company.key = p.key,
first adress left outer-join where company.key1 = adress.key1 and
company.key2 = adress.key2

first p1 left outer-join where company.key = p1.key,
first adress1 left outer-join where company.key1 = adress1.key1 and
company.key2 = adress1.key2

first p2 left outer-join where company.key = p2.key,
first adress2 left outer-join where company.key1 = adress2.key1 and
company.key2 = adress2.key2

first p3 left outer-join where company.key = p3.key,
first adress3 left outer-join where company.key1 = adress3.key1 and
company.key2 = adress3.key2
");

this query slows down the entire program
especially if i do:
get last q.

where is the problem?
what am I doing wrong?
 
Are you sure all index fields or the right index fields are used?

e.g. is the key from the adress tables adress.key1 adress.key2?

Just curious but do you have 3 different adress tables or are adress1, adress2 and adress3 buffers of the same table?

Casper
 
for each company

Without deep analysis, this expression requires the whole table scan. First of all try to rearrange your query.

btw,
what size of 'company' table is?
are you running the query using client/server or self client session? if you're on client/server, use 'fields' option it can reduce the network traffic.
 
we don't have alot to go on. it's pseudo code, we don't have any schema, indices etc.

you mentioned get last, could be indexed-reposition ?

try replicating the case on sports2000. post something we can compile so we can do more then guess.
 
adress1, adress2 are buffers on the same table.


i made this query now procedural, meaning

query-prepare("for each company...").

procedure next:
get next query.

find first adress1
if avail
find first person1
if avail

and so on...

it work's that way but a query would be better (because of sorting). but i really think it's the outer-join. the more outer-joins i make the longer i takes (exponential)

what exactly do you mean with deep analysis?
btw: thank you for your responses :)

edit:
changed "for each person" back into "for each company"
 
Back
Top