Subject RE: [firebird-support] Re: select count(*) took time
Author Hoang-Vu PHUNG
Hi Adam,

Thanks for your answer and your tips.
The thing is select count(*) on an oracle database
ist many times faster. And more strange, select
count(*) on a hsql database (database written entirely
in java) is also faster.

I wrote an application thats inserts data in databases

via jdbc. It took around 1.5 seconds for 100 rows
(really big record) on hsqldb and nearly 8 seconds !!
on firebird (with jaybird version 2.0). [I used

It is quite impossible to understand when I heard a
site using a firebird database of 47 Gbytes !!

Thanks for your pointers.

--- Adam <s3057043@...> a écrit :

--- In, Hoang-Vu
<hvu_phung@...> wrote:
> Hi all,
> I have loaded about 200'000 records in a table,
> that make nearly 800 Mega bytes.
> Now if i do select count(*), it takes nearly one
> minute to finish ! Why ???

Because every record needs to be read to know if **
** is allowed to count it. It is not as simple as
looking to see how
many records are physically present in the table or
index or that
would be quick. Deleted records, yet to be committed
inserted records
are all still in the table at the time, so unless you
expect your
count to contain all of those records, it had better
check you can see
them. The transaction that wrote the record version is
present on the
data page, not in the index so if the entire table
needs to be read,
then the quickest way to do that is to jump straight
to the data pages
and read them in storage order.

If you want an approximate count (not accurate but
often enough), look

Never use count when you mean want to check if a
record exists, that
is what the keywork 'exists' does really quickly.



Visit and click the
Resources item
on the main (top) menu. Try Knowledgebase and FAQ
links !

Also search the knowledgebases at



Technical support
Computer technical support
Compaq computer technical support

Compaq technical support
Hewlett packard technical support
Microsoft technical support


Visit your group "firebird-support" on the web.

To unsubscribe from this group, send an email to:

Your use of Yahoo! Groups is subject to the Yahoo!
Terms of Service.


Faites de Yahoo! votre page d'accueil sur le web pour retrouver directement vos services préférés : vérifiez vos nouveaux mails, lancez vos recherches et suivez l'actualité en temps réel.
Rendez-vous sur