How to optimise the performance of Microsoft Access Databases

Is your Microsoft Access Database running slowly?

Do Queries take for ever to load?

Read on for our great tips on optimising the performance of your Microsoft Access Database.

Optimising General Table Performance

To achieve the best performance results, apply the following guidelines for table design in your Microsoft Access Database:

1. Design tables without redundant data. A well-designed database is a prerequisite for fast data retrieval and updates.  If existing tables contain redundant data, the Table Analyzer Wizard can be used to split the tables into related tables to store the data more efficiently.
2. Choose appropriate data types for fields. Space in the database can be saved and join operations can improve by choosing appropriate data types for fields.  When defining a field, choose the smallest data type or FieldSize that’s appropriate for the data in the field.
3. Create indexes for fields that are sorted, joined, or used in queries. Dramatic improvements in the speed of query execution in Microsoft Access Databases can be achieved 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.
4. Indexes take up disk space and slow adding, deleting, and updating records. In most situations, the speed advantages of indexes for data retrieval greatly outweigh these disadvantages.  However, if the application updates data very frequently or if there are disk space constraints, the number of indexes may have to be limited.
5. In a multiple-field index, use only as many fields in the index as necessary.

Achieving Optimal Performance with Linked Tables

Although linked tables can be used as regular Microsoft Access Database tables, it’s important to keep in mind that they aren’t actually stored in the Microsoft Access database.  Each time data is viewed in a linked table, Microsoft Access has to retrieve records from another file.  This can take time, especially if the linked table is on a network or in an SQL database.

When using a linked table on a network or in an SQL database, follow these guidelines for best results:

1. View only the required data. Do not page up and down unnecessarily in the datasheet.  Avoid jumping to the last record in a large table.
2. To add new records to a large table, use the Data Entry command on the Records menu to avoid loading existing records into memory.
3. Use filters or queries to limit the number of records to be viewed in a form or datasheet. This way, Access can transfer less data over the network.
4. In queries that involve linked tables, avoid using functions in query criteria. In particular, avoid using domain aggregate functions, such as Dsum, anywhere in the query.  When using a domain aggregate function, Access retrieves all of the data in the linked table to execute the query.
5. When adding records to a linked table, create a form for adding records that has the DataEntry property set to Yes. When opening the form to enter new data, Access does not display any existing records.  This property setting saves time because Access does not have to retrieve all the records in the linked table.
6. Remember that multiple users might be trying to use an external table at the same time.   When an Access database is on a network, avoid locking records longer than necessary.

Optimising Multi-user Performance

When Microsoft Access Databases are used in a multi-user environment, the following guidelines show how to optimise the databases performance:

• Put only the tables on a network server, and keep other database objects on users’ computers. The database’s performance will be faster because only data is sent across the network.  Tables can be separated from other database objects by using the Database Splitter Wizard.
• Choose an appropriate record-locking strategy.

When editing records, Microsoft Access can automatically prevent other users from changing the record before the editing has been completed.

Giving one user exclusive Access to a record is called locking.  There are three locking strategies to choose from:

1. All Records. Access locks all records in the form or datasheet (and underlying tables) being edited for the entire time it is open, so no other user can edit or lock the records.  This strategy is very restrictive, so choose it only when only one user needs to edit records at any one time.
2. Edited Records. Access locks the record being edited, so no other user can change it.  It might also lock other records that are stored nearby on the disk.  If another user tries to edit a locked record, Access displays a  in the other user’s datasheet.  This strategy ensures that  the record editing is always completed by one user, before another user  can add their changes.  This is a good choice if editing conflicts a rare.
3. No Locks.  Access does not lock the current record being edited.  When the record is being saved where more than one user is editing the record, Access displays a message giving user the options of overwriting the other user’s changes to the record, copying their version of the record to the Clipboard, or discarding their changes.  This strategy ensures that records can always be edited, but it can create editing conflicts between users.
4. Avoid locking conflicts by adjusting Refresh Interval, Update Retry Interval, Number of Update Retries, and ODBC Refresh Interval settings (if applicable).
5. Convert the workgroup information file. While Access can use a workgroup information file from previous versions of Access, additional memory is required to do so.  To take full advantage of improvements in security and to reduce memory usage, always convert the workgroup information files.

Query Performance

There are several methods to improve the performance of query execution.  In addition to the following tips, use the Performance Analyzer to analyse specific queries in a Microsoft Access database.

1. Index fields on both sides of a join, or create a relationship between these fields, and index any field used to set criteria for the query. Avoid restrictive query criteria on calculated and non-indexed columns whenever possible.
2. When creating a query, add only the required fields. In fields used to set criteria, clear the Show check box to hide those fields.  Use field sorting judiciously, especially with non-indexed fields.
3. Avoid calculated fields in nested queries. When a query containing a calculated field to another query is added, the expression in the calculated field slows performance in the top-level query.
4. If the data doesn’t change often, use Make-Table queries to create tables from the query dynasets. Use the resulting tables rather than queries as the basis for the forms, reports, or other queries.
5. When grouping records by the values in a joined field, specify Group By for the field that’s in the same table as the field being totalled (calculating an aggregate on). For example, if the query totals the Quantity field in an Order Details table and groups by OrderID, specify Group By for the OrderID field in the Order Details table, not the OrderID field in the Orders table.
6. For greater speed, use Group By on as few fields as possible. As an alternative, use the First function where appropriate.
7. Avoid using domain aggregate functions, such as the DLookup function, in a query to Access table data. Instead, add the table to the query or create a subquery.
8. When creating a Crosstab query, use fixed column headings whenever possible. To specify fixed column headings, choose Properties from the View menu, select the ColumnHeadings property, and type the required headings, separated by the list separator, in the property box.
9. Use the ..And, the In, and the equal (=) operators on indexed columns. This helps optimise queries.

 The Performance Analyzer

• Open the Microsoft Access database to be optimised.
• Press the Database Tools Ribbon Tab
• Press the Analyze Performance button on the Database Tools Ribbon
• In the All Object Types list, click the database object to be optimised.
• Press the OK button

A list of three kinds of optimisations is displayed:

• Recommendations
• Suggestions
• Ideas.

When the item is clicked in the list, information about the proposed optimisation is displayed in the Analysis Notes box below the list.  Suggestion optimisations have potential trade-offs that designers should consider before performing them.  To view a description of the trade-offs, click on a Suggestion optimisation in the list, and then read the information in the Analysis Notes box.  Access can perform Recommendation and Suggestion optimisations automatically.  Idea optimisations have to be applied manually.

• Select each of the Recommendation or Suggestion optimisations to be performed, and then press the Optimise
• To perform all Recommendation or Suggestion optimisations, click Select All, and then click Optimise.
• To perform an Ideal optimisation, click that optimisation, and then follow the instructions displayed in the Analysis Notes

Find out far more about Microsoft Access databases on one of our highly rated training courses, scheduled regularly in Glasgow and Edinburgh.