I've looked at a number of different ways to do this, and the fastest I've found so far is to use proutil tabanalys. Using select count(*) is about the same speed.
The interesting thing about proutil tabanalys is it reports on every table, in the same time that it takes for select count(*) to do one table. It takes less than half a second to report on nearly 1 million records over 200+ tables.
So this makes me think that the information for each table must be stored somewhere, and that proutil tabanalys is just reporting on this information for every table. If so, how do I get this information for a specific table?
Or is there a better method altogether?
if i'm not mistaken, tabanalys will read both the index entries and records (data). select and for each queries will read both the index and data in most cases and just the data with a whole table scan (but only if you're using type 2 storage areas) but in no case will the query be satisfied with just the index entries.
by the way, are you using type 2 storage areas? which could cause a situation that is similar to a fragmented disk and might explain why tabanalys has similar performance to a single query.
since indexes are in some cases 1000 times smaller than the data, reading the index entries will be far quicker (in general index operations are much "cheaper" than operations that read data).
you could use the proutil idxblockreport to calculate the number of index entries for a table although there can and probably will be stale index entries of deleted records that haven't been removed yet so it's not exact but it should be pretty close.
you can use the slib/utils/fast-rec-cnt.p in the standard libraries project at the oehive.org. i timed it at roughly 1 second per 500,000,000 records. ymmv
<code>
define var i as int no-undo.
run slib/utils/fast-rec-cnt.p( "<physical dbname>", "table", output i).
message i.
</code>
hth
I had tried using proutil idxblockreport and fast-rec-cnt.p but they are slow (in comparison to tabanalys) and give inaccurate results (e.g. 1 second to report on 807 records when there are actually 1540)
No we're not using type 2 storage areas at the moment - we are planning to look into this at some point.
Type II storage should be very high on your priorities list. You will not be able to leverage many of the new performance enhancements introduced in newer versions until you do.
One such feature is the TABLE-SCAN option on a for each. This reads the records in a table without an index, rather in the order it finds them meaning it can be a lot quicker than an indexed for each or even a count(*).
But there are other benefits, like improvements to the dump and load performance, and also the general benefits of Type II storage that you're missing out on.
Yes there's a bit of work required to implement it, but it shouldn't be too difficult and there's a lot of advice out there on how to go about it.