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