On Fri, 7 Jan 2005 08:33:57 +0100, Carsten Schäfer wrote:

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

I work also with SQL server and there will exist queries that
cannot run without a serious analysis about resources and
indexing....
And see that FB 1.5.x optimizer is much better than Interbase 6.x
and prior and FB 1.0.x...