harpyopsis
New member
- Local time
- Tomorrow, 02:23
- Joined
- Oct 15, 2009
- Messages
- 5
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!
phil
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!
phil
