Query efficiency

[Tekime]

Hi all,

I hope this belongs here instead of the forms forum.

I am nearing completion of a chargeback database I have created in Access 2000. The database has ~100,000 records in the orders table and ~100,000 records in the transactions table. Both of these figures will most likely balloon to around half a million records by near end. I have split the DB and access is over a local network of Win2K and NT 4 machines.

While most of the processes required of the DB are data entry related, I am struggling with the most efficient method of searching for records.

I am trying to centralize the approach by creating a single continuous form with which to find chargebacks. Each line will have a button to open various forms for data entry and detailed informational purposes.

In a situation with many records and a fairly dependant schema (lots of relationships and dependant fields), would it be more efficient to set the recordsource of the continuous form to retreive info on all of the existing chargebacks in the database, or to load up an empty form with a search box on top, and perform a search query with a much smaller recordset being returned, yet more often?

I know this isn't a balck and white issue, but this is my first big project and I could use some tips/pointers on how to weight my options. Also if anyone could recommend a good way to monitor the network load, query time, etc. to compare performance of various methods it would be a huge help.

I apologize if this isn't entirely clear. I'm a bit under the weather today and probably not making perfect sense

Thanks

 

[Tekime]

Thanks for the reply Pat. In all honesty, I can almost guarantee we won't be moving away from Jet within the next few years. Chances are we'll backup a bunch of records and strip them from the database when it becomes unwieldy. This isn't a choice I have any influence on, unfortunately

I take it from your reply that record retrieval times won't be affected hugely using Jet if I don't specify any criteria. Is this due to the indexes already being loaded then?

Another question -- hope this isn't too far off topic. Is there any real speed difference between using a sub-form in a form, and directly including the fields from the sub-form in the parent form's recordsource query?

For example, one of my primary forms displays fields from almost all of my tables. One of the tables stores customer order information with ~30 fields which need to be displayed. I am tempted to use a sub-form for the order info to decrease the complexity of the parent form's recordsource query, but I'm not sure where the best performance lies.

 

[Tekime]

Well there are many joins in the queries I am most concerned with. I think I will limit my 'lookup form' recordsource by dates to limit the number of records returned.

Although, there will never be hundreds of thousands of records returned. We will be using the system to track chargebacks, so we might be looking at hundreds OR thousands of records returned, although many of the fields are dependent on entries in tables containing a few hundred thousand records.

Thanks again for your help, I'll keep working away at this as well as trying to benchmark the speeds of some of these queries to get a more detailed idea.