Subject Re: Upgrade to FB2.01 and performance drops
Author Adam
--- In, "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?


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.


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.