Subject Re: select count(*) took time
Author Adam
--- In, Hoang-Vu PHUNG
<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 ** YOUR TRANSACTION
** 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.