You could try to put your sub-query into a view or stored procedure
and then join it to your main query. This way the sub-query will have
to be run only once.

E.g. put this query into a view:
select aKey, count(aKey)
from AnotherTable
group by aKey

and then join like this:
SELECT * from Atable
inner join <view> on Atable.aKey = <view>.aKey and <view>.count > 1


--- In firebird-support@yahoogroups.com, "Maciek BOROWKA" <MBO@c...>
wrote:

> Hello,
>
> I have a problem with the performance of some of the queries I do.
> They are all basically of the same type:
>
> SELECT * from Atable WHERE AKey IN
> (select aKey
> from AnotherTable
> group by aKey
> having count(*) > 1)
>
> As you can see, I need to make a complicated subquery and then
> use it in a join to select some other data. An important point is
> that my subquery is not related to the main one (I mean, no
> common parameters). However, as far as I understand, firebird
> will execute the subquery for every row in the query in order
> to verify the "IN" condition. As you can imagine, everything
> is horribly slow (1 minute of a bi-xeon server) and thus quite
> inacceptable.
> My question is : what is a "firebirdish" way of achieving the wanted
> result? I tried to create a view with the subquery but the problem
> is the same.
>
> It's firebird 1.0.3, by the way. I didn't check it with 1.5.
>
> Best regards,
> Maciek Borowka