Subject Re: Query with indexes used takes over 21 minutes
Author Svein Erling Tysvær
Well, yes, Carsten,
if you want to not restrict your user whatsoever when it comes to
searching and joining not-too-small tables, and that user does not
have any knowledge about Firebird or how to read plans, then I guess
Firebird is not usable.

Mind you, I think the only sensible way to handle such a case would be
to appoint a database specialist to write the queries for all your
users. Though this is a case where I think the ability to stop running
queries in Firebird would be desirable, telling the user to press
cancel if it takes to long is better than just having to wait.

If you write the reporting tool and has knowledge of the database,
then it may be possible to help your users a bit. Adding +0 in all
cases is, as Ann showed, a bad choice (although I didn't quite follow
her logic, my experience says that using +0 prevents using an index
for the field being added to, it does not influence indexes on the
field on the other side of the equal sign). Preparing the query,
reading the plan and then see whether you should do something or
totally reject the execution of the query in question, could in some
cases be possible (e.g. reject queries which have NATURAL on a table
of reasonable size that is not the first table of the plan, or using
several indexes on one table when you know that one of them is almost
unique), even though it would only help to a certain extent.


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