FindFirst Alternative

[cable]

I've got a model that needs to do a lot of date lookup's on largeish (200k rows) tables, at the moment I load up readonly recordsets and use findfirst, but this is proving very slow.

What alternatives are there? Would loading into arrays be an option (worried about memory there). or any other tricks? saving the querys into tables? or sommit?

 

[modest]

Need more info. First of all "very slow" is how long? 15secs, 5mins, half an hour? The larger your db gets, the slower its going to get, but how slow is slow to you. We can't tell you you can make it faster, w/o knowing where you're at.

Second, how are you looking up the dates? Are you creating queries in VB? If so, having an already made query in querybuilder is generally faster.

Third, what do your tables look like? How many fields do they have... how many are you using. It might be better to create a new table with only the fields you need to look up.

Finally, there are a variety of ways that you can speed up your db by programmatically using different function calls, directly declaring your variable types, and more or less taking the time to do everything right. What I mean by this is that there is more than one way to do something, but a "best" way does normally exist.

In speaking about your queries.. I had a query that would hang for about 3-7mins. By changing how the query looked up data it now performed at about a 30sec pace, which is pretty drastic when you're sitting at the computer and it's all automated.


Just post a little more info and we'll try to help.

 

[cable]

too slow I just ran it on my home pc and it's far quick, but then thats going from 256mb to 1gb of ram so thats probly it.

At the moment its taking 1-2mins to do one main record on the work pc's.

The child tables aren't complicated, at most 9 rows, and I tend to use 2/3 of them, the date field (or start/end dates) and the amount field.
All the child tables link to main table via two fields that make the key, the child tables don't have primary keys, but the main one does. I'm going to try adding a PK now.

Because the users don't always want to report on the entire data set I narrow the set by creating a table with the the fields that make the key of just the selected data. Then I use queries (with group by and sums to sum the amounts by date) to select just the data I want from the child tables.

I'm thinking that making tables from the queries and using the tables instead will be quicker in the processing, even if the inital phase is longer.

 

[Epic]

- indexing the search fiels may improve performance
- try to convert date to string. use that for grouping
- use built-in functions not yours
- if you need merged strings as search field in the another query you may alter the sql and you will need less query's.

As modest said ... 'slow' is relative.

 

[MarkK]

As an alternative to using Find functions in a recordset, refine your SQL WHERE clause to only return the records that you need.