Carsten Schdfer wrote:

> Now i have a query were indexes are used but it take over 21minutes on my
> machine (Firebird 1.5.2, WinXP, on Pentium 4, 2.66Ghz, 1GB RAM).

With care, I can make an update that was running for 36 HOURS complete
in 12 minutes - I just depends on how you configure the question.

> SELECT count(t_apos.id_apos)
> FROM t_apos
> JOIN t_auftrag ON f_id_auftrag + 0 = id_auftrag
> WHERE t_apos.f_id_anliefer = 10
> AND t_auftrag.f_adatum between '07.12.2004 00:00' AND '08.01.2005 11:23'
> AND t_apos.f_id_auftrag + 0 != 0
> PLAN JOIN (T_AUFTRAG INDEX (IND_AUFTRAG_ADATUM),T_APOS INDEX
> (RDB$FOREIGN62))
> RDB$FOREIGN62 = foreign key on t_apos.f_id_anliefer
> PlanAnalyzer says that Firebird need 82.194.000 indexed reads on t_apos
> and 2.280 indexed reads on t_auftrag
> Result of count is 769,
> t_apos has 221.063 rows and t_auftrag has 97.751 rows.

Then I'd be using t_auftrag and only JOINING t_apos you seem to be
filtering on a subset of t_auftrag? ( I often find switching the JOIN
order makes the most difference )

> Is this normal behaviour of Firebird ?
> I can't believe that someone can use Firebird for reports where the user can
> make his own query, because it's so easy to break firebird.

I've seen queries that can run for hours where COUNT is used without
watching where it is used. And not only on Firebird. I just restrict
access to information, OR provide trigger generated counts where the
information is used often.

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services