At 01:29 AM 25/02/2006, you wrote:

>I have a table with 586,000 records. When I try to get a record count
>using select count(*) from table, it takes hours. Why is this?

Because a lot of garbage has accumulated and select count(*) (because
it walks the whole table) is causing GC to happen. So, given this
endemic problem in your system, it won't hurt at all to run this
query when nobody else needs to access the database.

When it's done, you'll find that the next time you request this
query, it will be a sub-second query.

However, this little trick is a Band-Aid. You'll need to fix
whatever your applications are doing to prevent background
GC/cooperative GC (depending on the server model) from doing its
stuff. That means curing the disease, so that you're not going to
suffer the pain and create a need to treat symptoms.

>How can I get a quick count?

a) with extreme reservation. Select count(*) on an unrestricted
query is not a recommended thing to do in a MGA database (except for
relieving the symptoms of problems caused by writing applications
that don't understand the mechanics of MGA..)

b) with no dependence on the result. The count will be "accurate"
only within your transaction and only in a snapshot transaction. So
never use it to calculate a new value for a key or to acquire a
number that you are going to store or use in a calculation. In a
Read Committed transaction, it will be inconsistent even within your
transaction, since the facts could change between when you start
counting and when you finish.

In other words, find another way to do whatever it is that you're
relying on a "quick count" for, unless it happens to be a statistical
analysis of some kind. For a statistical scenario, perform the
entire analysis inside a single snapshot transaction.

./heLen