Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Designing Tables to Optimize Performance

Now that you have seen the changes you can make to your environment to improve performance, take a look at the changes you can make to your data structures to optimize performance. Such changes include eliminating redundant data, using indexes, selecting appropriate field data types, and using various query techniques.

Tweaking the data structure is imperative for good performance. No matter what else you do, poor data design can dramatically degrade the performance of your application. All other optimization attempts are futile without proper attention to this area.

You can spend days and days optimizing your data. You must carefully think through and analyze these data changes. You will often make data changes over time as you or your users identify problems. Such changes can include those in the following sections.

Why Be Normal?

In essence, "be normal" means normalize your tablesthat is, consolidate common data in related tables. Processing the same data that appears in multiple places can significantly slow down your application. This is because of both the volume of data that is generated, as well as the need to update all copies of the data whenever the data changes. Suppose a company address appears in both the Customers table and the Orders table. If the company address changes, it must be changed in both the Customers table and in the Orders table. This information should be included only in the Customers table. Queries should be used to combine the address and order data when needed.

I Thought You Just Told Me to Normalize

When it comes to performance, unfortunately, there are no hard-and-fast rules. Although most of the time you gain performance by normalizing your data structure, denormalizing your structure can help at times. This generally is the case when you find yourself creating a particular join over and over again. Another example is an accounting application in which you need to be able to readily see the total amount that a customer owes. Rather than evaluating all the open invoices each time you move to a customer record, you can store the total amount that the customer owes on the customer record. Of course, this requires that you update the summarized figure whenever the customer is billed or makes a payment. In summary, you can try denormalizing the data to see whether dramatic performance improvements result. Remember that denormalization has definite downsides regarding data integrity and maintenance.

Index, Index, Index!

It is amazing how far an index can go in improving performance. You should include any fields or combination of fields on which you search in an index. You should create indexes for all columns used in query joins, searches, and sorts. You should create primary key indexes rather than unique indexes, and unique indexes rather than non-unique indexes. It is not necessary to create an index for the foreign key field in a one-to-many relationship. Access automatically creates the index when you establish the relationship. Furthermore, there is no benefit to creating an index on a field containing highly repetitive data. An example is a state field in a customer table where all the customers are located in one of two states. Although you can overuse indexes, when you use them properly, the performance improvements rendered by indexes are profound.

CAUTION

Although indexes can dramatically improve performance, you should not create an index for every field in a table. Indexes do have their downside. Besides taking up disk space, they also slow down the process of adding, editing, and deleting data.

TIP

In a multiple-field index, index on as few fields as possible. Searching through multiple-field indexes can dramatically degrade performance.

NOTE

Client/server optimization strategies are covered in detail in

Alison Balter's Mastering Access 2002 Enterprise Development .

Select the Correct Data Type

When defining a field, select the shortest data type available for the storage of the data. If you will be storing a code between 1 and 10 within the field, for example, there is no reason to select Double for a numeric field. Although Double would work, it would require unnecessary storage space as well as unnecessary processing time. On the other hand, make sure that you always leave room for growth of your data. For example, many people often select Integer, not realizing that they should have selected Long Integer.