Adam ha scritto:

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

A few days ago I posted a message
(http://tech.groups.yahoo.com/group/firebird-support/message/86039)
about a query using 'IN' and a subquery: it was deadly slow.
I am using V2.0.1 and I don't know how it could work with V1.5, but if
you want I can send you a sample database (less than 1 MB) that shows
the problem.
Just let me know.

Fulvio Senore