Retrieving data from database. Retrieve only when needed or get everything?

Question

I have a simple application to store Contacts. This application uses a simple relational database to store Contact information, like Name, Address and other data fields.

While designing it, I question came to my mind:

When designing programs that uses databases, should I retrieve all database records and store them in objects in my program, so I have a very fast performance or I should always gather data only when required?

Of course, retrieving all data can only be done if it`s not too many, but do you use this approach when you make sure that the database will be small (< 300 records for example)?

I have designed once a similar application that fetches data only when needed, but that was slow (using a Access database).

Thanks for all help.

Answer 1

I think it will be best to retrieve data when needed , retrieving all the records and storing it in object can be an overhead. And when you say you have a small database , retrieving the records when needed should not be an issue at all.

Answer 2

This depends a lot on the type of data, the state your application works in, transactions, multiple users, etc.

Generally you don't want to pull everything and operate on the data within your application because almost all of the above conditions will cause data to become non-synchronized. Imagine a user updating a contact while someone else is viewing that information from a cached version inside their application.

In your application, you should design the database queries such that they retrieve what is going to be displayed on the current screen. If the user is viewing a list of contacts, then the query would retrieve the entire contact table, or a portion of it if you are doing a paginated view. When they click on a contact, for example, for more information, then a new query would request the full details of that contact.

For strings and small pieces of data like what a contact list involves, you shouldn't have any speed issues working with a relational database like SQL, MySql or Oracle.