Ann W. Harrison wrote:

> Carsten Schäfer wrote:
>>
>> 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.
>>
>
> The short answer is that you did this to yourself by trying to
> outsmart
> the optimizer. The long answer follows.
>

...

> The plan I suggested above (find the t_auftrag first, then lookup the
> t_apos) would start by finding 2.280 t_auftrags in the date range,
> then
> for each of them lookup t_apos on the foreign key. I have no idea of
> the distribution of the t_apos records on that key, but if the values
> were evenly divided there would be two or three t_apos's for each
> t_auftrag. If so, the total cost would be about 8.000 record fetches.
>
> That sounds like a plan to me.
>

You're absolutly right.
If i remove the f_id_auftrag + 0 the query is fast (1 sec).
The problem is that than many other queries are slow.
(see Message:Performance differences from 25.11.2004)
So i really come to the conclusion that Firebird is not usable for reports,
where the user can himself make the queries (user can pick fields from
different tables and can combine them with 'and' or 'or')
For every query there is a good optimisation, but sometimes the optimizer
finds the optimum for himself and somtimes i have to give hints.
There is no concept that works for all kinds of these simple queries.

mfg
Carsten