Results 1 to 5 of 5
  1. #1
    u38cg is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    3

    Optimising lookups in a large fixed table

    Hi folks,



    I have a large table with the following set of fields:

    Age (integer 18-65)
    Series (int 1-5)
    Class1 (int 1-4)
    Class2 (binary - currently text)
    Class3 (binary - currently text)
    Class4 (int - 4 possible values)
    Term (int 0-45)
    Rate (float)

    Basically, what I need to do is query for Rate according to a combination of some of the other fields. Not all the fields are used for each query, but every query is guaranteed to be unique. The data is fixed and will not normally change. As you can see, when fully populated this is a biggish dataset, probably 120k rows or something of that order.

    At the moment, I'm using Find with a DAO recordset (called t):

    Code:
     
    qry = "Age = " & Val(r!Age)
    qry = qry & " AND Series='xyz'"
    ' etc
    t.FindFirst qry
    rate = Val(t!Rate)
    I have to carry out about 300,000 of these lookups, and ideally I'd like something that runs *fast*. I'm using a constrained test set of about 30000 rows at the moment and the slowdown is significant. I gather that Seek rather than Find is a better choice for speed, but frankly I don't understand the documentation, not being much of a programmer.

    Is there a better way to do this? I'm be no means fixated on using DAO; it was simply the first thing I got working so I've used it throughout my project.

    Many thanks.

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    am confused; 120k records and 8 fields is a small table. why 300k look ups ?? that's what is confusing.....

    but in any case the query also seems simple ; 2 criteria - Age & Series (although you display Series as a fixed value). A typical query in this case would run in the blink of an eye.....but of course 300,000 blinks might take a bit......

    label me as confused.

  3. #3
    u38cg is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    3
    Sorry, I'll try and clarify what I'm attempting!

    Basically I have 300000 records, which look like this:

    ID
    Age
    Series
    Other stuff
    A number

    My aim is to check that the number in the data is correct. It is calculated using the rate, which we look up, and other info contained in the record. The rate depends on age, series, some of the class fields, and the term.

    Am I being stupid here: would I be easiest to do

    Set t = CurrentDb.OpenRecordset("SELECT * FROM tblRates WHERE Age=blah AND Series=blah AND so forth")

    ?

    Because of the structure of the data, I can be certain that any such query is guaranteed to return exactly one record.

  4. #4
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    Well in your first post you display Rate as being a field in the table; but in the second post you do not and state that it is looked up. So it remains unclear as to the most efficient way to implement what you seek.

    Generically speaking - if you are attempting to verify a number (value) in a field, in every record - then you need to of course have that known-good value (rate?) joined to each record - - and compare using a calculated field.

    Hope it helps a little.

  5. #5
    u38cg is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    3
    Third time lucky! Right: my table looks like this:

    Code:
    Age|Series|Class1|Class2|Class3|Class4|Term|Rate
    29|3|1|Y|N|2|14|4.385
    30|3|1|Y|N|2|14|4.432
    ...
    I need to do

    Code:
     
    Dim t As DAO.Recordset
     
    For i =1 to 300000
    Set t = CurrentDb.OpenRecordset("SELECT * FROM tblRates WHERE Series='1' AND Age=29 AND Class1=3 AND Class4=2 AND Term=14")
    rate = t!Rate
    'Do stuff with rate
    Next
    The question is, what is the quickest way to get that rate out of the table each time round the loop?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. No Lookups in a Table
    By oleBucky in forum Database Design
    Replies: 9
    Last Post: 03-23-2011, 01:40 PM
  2. Replies: 1
    Last Post: 01-22-2011, 12:23 PM
  3. lookups and data selection help
    By benjammin in forum Access
    Replies: 3
    Last Post: 11-28-2010, 04:45 PM
  4. Access ADP & Lookups
    By sql_dan in forum Access
    Replies: 0
    Last Post: 06-09-2010, 04:25 AM
  5. Median and Mode for a large table
    By brandon in forum Access
    Replies: 0
    Last Post: 05-08-2008, 09:26 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums