[02/03] Dataset performance question

I have a database with 30K records, AC2K and Jet 4.0.

The user will have the ability to type the desired record and bring in the information (15 or so fields, no images, plain text and some dates).

So far I populate the dataset at form load, it takes some time and after that I use .find(key) to bring on the record. Question: Would it be advisable to populate the dataset only with the desired record using the SQL string each time, or ist it better performance wise the way I have been doing it?

Thanks.

30K is allot of records.

If the user needs to see the infomation from all those records at once (like in a datagrid) there is no other other way of doing it.

If the user generally only wants to work with a single record at a time. Do what you said and populate the dataset based on the desired record.

Or the other way is selecting and showing 1000 or so records at a time. By using the LIMIT keyword or a where clause

It all depends on what the User will do with the selected record. Ideally I will bring up only those records from the database that are required. Fetching whole data at once is not advisable.

Ok, here is how the user is working with the DB:

1. User starts with 0 records in the DB.
2. Creates half a record by entering some data.
3. User can keep on creating half records without completing one.
4. If user wants to complete a record, will go and find the first half by typing in the number in a textbox or using a combobox filled with half records.
5. User might check out the completed records using another combobox.

They are esencially working with 1 record at a time, but can check out whatever record they want. What are your thoughts?

Thanks.