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

This doesn't make sense in this case. Given the transaction numbers,
there are no old transactions holding old records. Therefore all twenty
index entries point to good, countable items, and there are no index
entries pointing to old, uncountable stuff.
Perhaps there is a problem with count(), and it was always assumed in
every case to be a facet of "you can't count on an MGA system."

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling
Tysvaer
Sent: Tuesday, August 08, 2006 6:19 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Is there a way to optimize a count()? or
am i doing it the wrong way?

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!

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the
main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links