Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Location
    Bali
    Posts
    9

    Unanswered: Counting large ADODB recordsets

    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

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Depending on what you want to do with the recordset (apart from counting its rows) you could try to open it with several different options and several different types (ForwardOnly, KeySet, Dynamic, Static, UseServer, UseClient, Locks etc.) and try to find the fastest that fits your needs. Eventually you could also try with a DAO recordset that can be faster in some situations.

    Finally you could populate the TreeView "on demand", i.e you populate a level of the Treeview that is not already populated, only when you open its parent level. This way, the TreeeView is always populated on sub-level ahead of the current one but you don't need to fully populate it from the beginning.
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    without knowing your SQL would a simple COUNT predicate assist in your SQL
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2009
    Location
    Bali
    Posts
    9

    Solved!

    Dear Sinndho and Dear Healdem!

    Thank you so much for your very quick and helpful replies: we can call this solved!

    The COUNT sql did it this way:

    SELECT Count ([Taxo20Species]![SpeciesID]) as Expr1
    FROM (....21 INNER JOIN....)
    WHERE Taxo11Order.OrderID = MyLng



    Results now pop-up as fast as it takes to click on a TV node, at any level!!!

    ADO or DAO run it at roughly the same speed. adUseClient, adLockReadOnly, adOpenStatic for ADO. The key was really to use the right SQL predicate!!!

    I love SQL!

    The huge Phylogeny TV doesn't cause any problem at all as, like Sinndho very rightly pointed out, it is populated on demand only. Good ol' MSComCtlLib! Very unlikely that a user ever fills its limit of some 30,000 nodes, and then there is a msgbox warning.

    This COUNT in real time was bloody important: we work on Taxonomy data gathered by an int'l institution, and this now make it so easy to detect trends and discrepancies from the original data bases!

    Thank you so much to you both, and greetings from Bali!

    phil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •