- database solutions and downloads for microsoft access

Query Optimization

Optimizing Query Performance:

Most RDBMS products have an optimizer built-in. This allows you, as the user, to focus on creating a statement of what data you want to see, and the optimizer figures out the most efficient way to retrieve the data. The optimizer considers factors such as the size of the table(s), what table joins are involved, whether or not indexes are available, and the physical clustering of the stored data.

However, there are additional things you can do that will enable your database queries to run faster. These include:

  • creating indexes
  • restricting the data (setting criteria) before other operations are performed
  • watching how you group information and use open-ended conditions
  • considering denormalizing some tables

Creating Indexes

Creating indexes will probably give you the most significant performance gains. Without an index, all the records in a database table have to be read before the records can be retrieved. With an index on a referenced field, only the index has to be read before the data retrieval can begin. Remember that the primary key is an index so you do not need to create one for that field or combination of fields.

You should create an index on fields:

  • you will use in criteria;
  • you will use in joins (such as your foreign keys);
  • on which you will group data;
  • on which you will sort data; and
  • on which you will perform summary operations.

As a guideline, you should have from one to four indexes on each table. There is a down side to indexes in that having an index on a field can slow down changes to data in that field. That’s because the index must be updated every time the data is changed. So avoid indexes on frequently updated fields.