This project has moved and is read-only. For the latest updates, please go here.

Table Record Count

May 16, 2011 at 7:49 PM

How do I get the number of records in a table?

May 16, 2011 at 8:20 PM

The completely accurate way is with JetIndexRecordCount. That API walks all the records so it will be slow on big tables.

You can efficiently estimate the number of records with JetGetRecordPosition. Using multiple samples will improve the accuracy, see this: http://blogs.msdn.com/b/laurionb/archive/2009/02/10/cheaply-estimating-the-number-of-records-in-a-table.aspx

If you are writing an application from scratch you could explicitly store the number of entries in a globals table and use JetEscrowUpdate to increment and decrement the count. That would require extra work but would give completely accurate results quickly.

May 16, 2011 at 8:26 PM

OK. An estimate is actually acceptable in the scenario I am working with. Out of curiosity, why is the record count not maintained by the database?

May 16, 2011 at 8:36 PM

The problem is that maintaining a record count can easily become a concurrency bottleneck because the count will be stored separately from the record being updated and all modifications on the table will need to update the same count. Most applications don't require accurate record counts and they don't want the overhead of keeping an accurate count.

To keep an accurate record count each thread that inserts or deletes a record will have to modify a per-table counter and that modification will have to be logged for crash recovery. Although you can use something like JetEscrowUpdate to avoid long-term locks the count will still have to be latched for a short period of time for the increment/decrement operation, which can lead to lock convoys. The convoy problem can be solved by having several separate counts (e.g. one per processor) and summing them to get the true count, but the code starts becoming quite complex.