--- In firebird-support@yahoogroups.com, <Hemant.Sapre@i...> wrote:

>
> Hi,
>
> Actually I meant that the COUNT on any table takes a huge amount of
> time. It is further compounded by the GROUP BY clause.
>
> For the following query it takes 8 seconds to execute
> select USER_KEY, COUNT(1) from FUND_SELECTION
> GROUP BY USER_KEY
>
> The total count in the table is 49,175 records only.
>
> When the table size grows to millions, performance deteriorates further.

http://groups.yahoo.com/group/firebird-support/message/67430

Read the first paragraph

Count is a slow operation period. It is fast on some systems because
those systems count the number of nodes in the primary key index etc,
or even faster still simply store the number of records of each table
in some sort of system table.

The index in Firebird contains records that have been inserted but not
committed, have been inserted and committed but not yet visible to
you, have been deleted but not committed, have been deleted and
committed and not visible to you but still visible to some other
transaction etc.

So Firebird could look at the index, but it would then have to visit
the data page anyway to see whether it needs to count that particular
record, so it saves a bit of time and doesn't even bother reading the
index (unless it is a where condition that knocks out a lot of records).

This post shows a similar problem from the other day. It can easily be
adapted to your scenario, and used correctly will provide sub second
results with little overhead.

http://groups.yahoo.com/group/firebird-support/message/67284

Adam