Hi Luciano!

No, there is no way to force Firebird to take shortcuts, unless you're
mad enough to change the optimiser.

One way to solve problems like you're experiencing, is to train the
programmer to carefully design and program the way Firebird prefers
queries to be. And we - partially - try to do this on this list. Give
us some example covering your problem (ideally the SQL statement, the
plan and some explanation - like this is an almost unique field or
this is an indexed Boolean field) and we'll try to see if we can
rewrite it more "Firebirdy". Then it is simply a matter of testing the
code and if it works, then learning how to write and think SQL this way.

Set

--- In firebird-support@yahoogroups.com, "Luciano" wrote:
> Thanks Adam and Svein for the answers. I tried the changes you
> suggested, but it kept taking the same 20 seconds. But when I
> defined the T2.CODREF as an index, as Svein said, it started to run
> instantaneously. The only problem is that on the subselect, in truth
> I have to make a JOIN with a third table in order to add another
> filter. The join is between two indexed fields, but when I add this
> JOIN, the query takes more than 20 seconds again. I simplified the
> SQL here because the main problem is, as Adam said, it executes the
> subselect for each record, and I was wondering if there was another
> way to do that. I didn't want to use a stored procedure, because
> I'll have to make one for each SQL like this, and I'll need many of
> them. Do you think there is a way to force the subselect to execute
> only once or something like this in order to make it run faster?
>
> Thanks again,
>
> Luciano Enzweiler.