• Design tables without redundant data. A well-designed database is a prerequisite for fast data retrieval and updates. If existing tables contain redundant data, you can use the Table Analyzer Wizard to split your tables into related tables to store your data more efficiently.

• Choose appropriate data types for fields. You can save space in your database and improve join operations by choosing appropriate data types for fields. When defining a field, choose the smallest data type or field size that's appropriate for the data in the field.

• Create indexes for fields you sort, join, or set criteria for. You can make dramatic improvements in the speed of queries by indexing fields on both sides of joins, or by creating a relationship between those fields and indexing any field used to set criteria for the query. Finding records through the Find dialog box is also much faster when searching an indexed field.

• Indexes aren't appropriate in all cases, however. Indexes add to the size of the .mdb file, reduce concurrency (the ability of more than one user to modify a page at the same time) in multiuser applications, and decrease performance when you update data in fields that are indexed, or when you add or delete records. It's a good idea to experiment to determine which fields should be indexed. Adding an index may speed up a query one second, but slow down adding a row of data by two seconds and cause locking problems. Or it may add negligible gains depending on which other fields are indexed. For example, adding an index to a PostalCode field may provide very little performance gain if a CompanyName field and LastName field in the table are already indexed. Regardless of the types of queries you create, you should only index fields that have mostly unique values.

• In a multiple-field index, use only as many fields in the index as necessary.

Back to Top