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):
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.Code:qry = "Age = " & Val(r!Age) qry = qry & " AND Series='xyz'" ' etc t.FindFirst qry rate = Val(t!Rate)
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.