Aage Johansen wrote:

> I would start by "removing" the index on either F.FTGSTAT or F.USTAT, _and_
> either A.AESTAT or A.AETYP. An index on A.ORGNR might be a good thing, and
> both A.AESTAT or A.AETYP could be "removed" (depends on selectivity for
> A.ORGNR).

Thanks, it worked. Exec time dropped from 21 hours to 34 seconds. (I
already had ORGNR indices if you look carefully.)

I must say that one of the most frustrating aspects of Firebird is its
unability to generate an appropriate plan for this kind of queries. It
goes against all sanity that (read) query performance would *increase*
by *removing* indices. (Assuming you're a "regular" user who doesn't
know the inner workings of the DB engine.)

As I understand it, the problem is that it will repeat the subquery for
each record in the outer query and that the low index selectivity of the
subquery indices causes these subqueries to execute rather sluggishly.

But I don't really understand what the problem is. The engine knows how
the outer query and the subquery are correlated. So it should be able to
run the subquery once ignoring the correlating conditions, order the
result set on the correlated columns, and do some kind of single-pass
loop. What am I missing?

Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64