Counting large ADODB recordsets

[harpyopsis]

Hello all!

I have a serious problem counting records in a large ADO rst in real time (user traveling on a 24-level MSComctl TreeView).

The underlying data is a mdb file with 21 related tables (all one-to-many) representing the phylogeny of (all?) living Species (1.4 Gigs).

Table1 ("Taxo00K") contains 8 Kingdoms, down to Table 20 ("Taxo20Species") which contains 1.1 mil. Species ID's. In between are 19 Tables with the 120 Classes, 1100 Orders, 7500 Families, Tribes, 110,000 Genera and so on. A long, -maybe too long, chain of 21 Tables...

All ID's, look-up and Sort_order fields are properly indexed.

Feeding the ADO rst with a very short SELECT (just the SpeciesID field), a necessarily huge FROM (cascading "INNER JOIN") and a simple WHERE clause ("FamilyID = MyLng") works very fine: RecordCount for some 1000's records is done in milliseconds.

Problems arise when we need RecordCount for higher classification levels (e.g. the Order level), which may amounts to 100,000 records, and which is 10 steps above the bottom "Species" Table. That process can need 10 seconds!

Generating a public rst at start-up, - or a private rst on Open (with the one mil. Species on which to perform rst.filter when needed), takes around 18 seconds. Way too long!

Is there any method to improve this "Count" speed???

Months ago, I had the upper classification levels ID's duplicated in the last "Species" Table and it performed well, but 1,000,000 times 21 long Int's is too heavy.

ADO rst.filter and rst.sort work great, but RecordCount is far behind Dcount!

Out of despair, I tried to fill the Dcount's domain with my SQL strings. Useless!

Last: this Taxonomy Table is a Replica, connected to a front-end mdb/mde file with forms and modules for biodiversity and GIS.

Any hint would be so highly appreciated!



Many thanks!

[dcrake]

When using large recordsets it is imperative that fields used for criteria conditions are primary keys from the source tables not the foreign keys in the target tables. Even with 1:m relates by querying the 1 side increases the return rate as it pulls the many records into it.

David

[harpyopsis]

You are absolutely right David! The speed increase is obvious when you work on the primary keys of the source Tables! Have to!

The solution to my problem was also very simple: a "SELECT Count []![] as Expr FROM (..Inner Join...) WHERE TableX.Rec_ID = MyLng"!

Instant results...

I now stop expecting too much from RecordCount!

Have a very nice day in UK and thank you so much!

phil

[RuralGuy]

Excellent! Thanks for sharing your solution. Follow the link in my sig if you want to mark the thread as Solved.