Counting a lot of rows is slow in Firebird, it has to visit every record
that it could possibly count to find out whether it should be visible to
the current transaction. Hence, you should only do this when it is
necessary. I haven't done any pagination, but it doesn't sound like too
interesting information, and I'd suggest that you simply drop the count.
Alternatively, you could write a stored procedure, return the rows and
e.g. count the first 200 records (that is easy to do in a stored
procedure). In general, I find the Google counting saying 'matches 1 to
10 out of approximately 5,483,700 matches' to tell me nothing more than
that I specified a too wide search and that I spelled all words
correctly! If it is important that the counting is correct, and that it
makes a difference whether the result is 123,223 or 159,206, then you
simply have to tell your users to wait.

There is a shorter way to get an approximate count of records in a table
(not in a query), though I am unable to find it now (I think it was on
fbtalk.net, but all I get now is an MySQL(!) error.

HTH,
Set

Fabio Gomes wrote:

> I run 2 queries, one that just counts, and another that returns just 20
> results, the only purpose of the count is to generate the paging numbers
> the query is very very fast.. some milliseconds.. but the count takes a lot of
> time.
>
>> SELECT count(I.vnd_cod)
>> FROM items_venda I
>> JOIN vendas N ON (N.vnd_cod=I.vnd_cod)
>> JOIN clientes C ON (N.cli_cod=C.cli_cod)
>> JOIN vendedores V ON ( N.ved_cod=V.ved_cod)
>> WHERE I.pro_cod='1'
>>
>> I m using it on php, so this code is just for the pagination, the main
>> query will just select 20 rows, no more than it. but i need a count of all
>> the rows so i can know how many rows the query returned, and pass it to my
>> pagination function.
>>
>> I m asking it cause currently we have just one company using this
>> application, but my boss said that almost sold it to another company that
>> have a lot more data then the current one, so i m beggining to worry about
>> the performance of some stuff, cause i use this in almost all reports, this
>> one is the worst in performance, but wating 20 seconds to show up 20 rows is
>> too much!