You are probably right Adam, in that the count is not the culprit. But I
think Fabio joins four tables, count x rows and then select & display 20
of them at a time. I don't know how many digits there are in 'x' and
agree that the count by itself cannot be the cause if it is a reasonably
small number (whether that small number is 20 or 10000). But using a
count for PAGINATION sounds like a bad idea unless you are certain that
the number is limited and that your select statement is not all too
complicated.

I almost wish it had been possible to write something like COUNT(*, 500)
to count up to 500 and return 500 if more than 500 was available. Though
I don't know if I'd ever use it if it was available ;o).

Set

Adam wrote:

>> every case to be a facet of "you can't count on an MGA system."
>> I would prefer to say: "You shouldn't COUNT on an MGA system"
>>
>
> I wouldn't go that far. I would say that 'COUNTING is an expensive
> operation on a MGA system and not a substitute for an exists check'.
>
> I don't think the problem here is the count itself, it is a bit of a
> 'red herring'.
>
> Whilst getting a count of the number of records is more expensive than
> in a locking database, I think everyone here would agree that 20
> seconds to return a count of 20, ** SOMETHING ELSE ** is causing the
> slowness.
>
> I would expect that retrieving a count is less expensive than the
> corresponding select statement, because there is less fetch time, but
> all else being equal, the prepare and execution time are identical.
>
> As a test I just counted 298700 records from a test database I have
> loaded. It took
>
> Prepare : 1 ms
> Execution : 446 ms
> Fetch : 9 ms
> -------------------
> Total : 456 ms
>
> This was a simple select count(*) query with no joins. There is no
> garbage in this table because it was recently restored. From a locking
> database I would expect sub 20ms response because it does not have the
> expensive execution time of a MGA database.
>
> When I add a join to another table with around 135000 records, the
> execution time blows out to 2.2 seconds.
>
> Clearly, the problem is not with the count performance in Firebird if
> it is taking 20 seconds to count 20 records. In a straight count,
> Firebird is capable of 600,000 records per second on my laptop. With a
> join involved it is still managing over 125,000 records per second.
>
> The problem here is most likely garbage.
>
> Adam