--- In firebird-support@yahoogroups.com, "David Savill" <david@...> wrote:

>
> Hi Trevor,
>
> We noticed the same thing in many queries when upgrading to FB2.0.1.
>
> We found queries that used to run in seconds took minutes. The issue

seemed to be

> around using IN and a subquery, eg "where foo IN (select foo from

bar)". Where ever we

> used IN and a sub query we had to change the query to use EXISTS and

it seemed to fix the

> problem.
>
> Only way we figured it out was to run each query though the

performance analysis in

> ibExpert and see what it was doing.
>
> Has anyone else had this issue?

@David,

I would expect the issue with queries containing 'NOT IN' subqueries.
They no longer use an index. See the release notes for details why.
Rewrite them as not exists.

@Trevor,

Any time the optimiser is enhanced it also opens up the potential for
it to make worse decisions. Write up a test case demonstrating the
problem (or at least post the offending query and plans from 1.5 and
2) and perhaps it may be possible to eliminate the offending indices
from being applied.

I don't know how you can judge that overall queries have dropped
(unless you use not ins everywhere), it only takes on bad egg to ruin
a cake. Perhaps the overall performance is sufferring because of one
or two queries.

Adam