Lester Caine wrote:

> 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.

It makes no difference when i change the join clause or when i select
t_apos.id_apos (without the count).

mfg
Carsten