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

--- In firebird-support@yahoogroups.com, "trtoms" <trtoms@...> wrote:
>
> We've just upgraded one of our servers from V1.5.3 to V2.01 and
> performance has plummeted.
>
> One particular query that runs on the old V1.5.3 takes around 7
> seconds, but this has shot up to over 600 on V2.01! Overall
> performance on most queries has dropped badly.
>
> We backed up the database fo v1.5.3 and restored it into V2.01 using
> IBOConsole, and all superficially seemed OK, but users started
> complaining of poor performance almost immediately.
>
> We've tried using another server, and this also took over 300 seconds
> to run the query (it's a better server). We then uninstalled V2.01,
> reinstalled v1.5.3 on this second server, restored the database and
> the query ran in 3 seconds!
>
> Now, the only thing tht seems odd, is that when restoring the
> database with IBOConsole, V1.5.3 shows messages "gbak: restoring
> index IDX_XXX", while V2.01 shows messages "gbak: activating and
> creating deferred index IDX_XXXX".
>
> The performance drop would be consistent with there being no indexes
> or inactive indexes in the newly restored database. However, all the
> indexes show as active in IBOConsole.
>
> Any clues to what is happening here?
>
> Thanks,
> Trevor
>