Sounds like Firebird 1.5.3 used a suboptimal plan. Can you tell us
about the plans and the tables?

I guess your SQL could be changed to

select
USERS.USER_NAME,
USERS.USER_ID,
count(MATTER_EVENTS.MATTER_EVENT_ID) as theCount
from USERS
join MATTER_EVENTS on USERS.USER_ID=MATTER_EVENTS.PERFORMER_ID
join MATTERS on MATTER_EVENTS.MATTER_ID=MATTERS.MATTER_ID
where MATTERS.STATUS_ID=2 and
MATTER_EVENTS.B_IS_ACTIVE=1 and
MATTER_EVENTS.DATE_DONE is null and
MATTER_EVENTS.DATE_REQD >= current_date
group by USERS.USER_NAME, USERS.USER_ID

though this would of course not get those users that didn't have
anything to count (theCount = 0), so it isn't quite identical and I
assumed DATE_REQD to already be a DATE value (dialect 3). Does this
help the Firebird 1.5.3 performance at all or is it identical to the
one you already mentioned?

One thing I do know Firebird 1.5 often "gets wrong", is that it
tends to use too many indexes if they are available. Are any low
selectivity indexes used in the plan?

I'm very happy with the performance improvement you report, but it
also suggests that things aren't quite ideal in 1.5.3. I haven't
actually tried Firebird 2.0 yet, but excepting new features like
expression indexes, a 35 times speed improvement sounds a bit too
much to be purely attributable to the excellence of Firebird 2.0 (or
has there been a major improvent in how COUNTs are done?).

Set

--- In firebird-support@yahoogroups.com, Russell Belding wrote:
> While converting SQL for a FB 1.5.3 to a FB 2.0 RC1 application I
> timed three sequential select statements. One follows and the two
> others are like it. They are used to report on done, pending and
> overdue tasks that have been scheduled.
>
> select
> USERS.USER_NAME,
> USERS.USER_ID,
> (select count(MATTER_EVENTS.MATTER_EVENT_ID)
> from MATTER_EVENTS
> join MATTERS on MATTER_EVENTS.MATTER_ID=MATTERS.MATTER_ID
> where (MATTER_EVENTS.DATE_REQD is not null) and
> (MATTERS.STATUS_ID=2) and
> (MATTER_EVENTS.PERFORMER_ID=USERS.USER_ID) and
> (MATTER_EVENTS.B_IS_ACTIVE=1) and
> (MATTER_EVENTS.DATE_DONE is null) and
> (cast(MATTER_EVENTS.DATE_REQD as DATE) >=
> cast(current_date as DATE))) as theCount
> from USERS
>
> FB 1.5.3 took 1 min 9 sec and FB 2.0 RC1 took 2 sec using the same
> data on similar servers. The Server and Client were the same PC.
> The FB 1.5.3 PC was a 3GHz P4 and FB 2 ran on a 2.8GHz P4. Each
> trial was ran twice with the same times.
>
> That is an impressive reduction in time.
> RB