When users complain that their Access reports and queries are running too slowly, your first inclination may be to check for problems with system or network performance. But increasing system response time would do little if the problem was caused by poor database design.
But how can you tell whether it's actually poor database design that's slowing your users down? First, run Access’s built-in Performance Analyzer tool. The Performance Analyzer analyzes each object in a database and then lists modifications that can be made to optimize performance. In many cases, you can have Access implement the optimizations for you automatically.
Follow this quick tutorial on using the Performance Analyzer to quickly analyze a problem database and automatically optimize database objects. In this example, I'll be using a database that has both queries and table relationships.
Working with the Performance Analyzer
To access the Performance Analyzer, open a database and follow these steps:
- Click Tools in the menu bar.
- Select Analyze in the drop-down menu.
- Click Performance.
This displays the Performance Analyzer window shown in Figure A.
The Performance Analyzer can be used to analyze any or all objects in your database. Each object type is represented by a different tab in the Performance Analyzer window. Clicking the appropriate tab lists the names of the particular objects in this sample database. (To list all objects at once, you would select the All Object Types tab.)
For this article, let's find out what we can do to make this database's queries run faster. Click the Queries tab and then click the Select All button to select all the queries. Clicking OK displays the Performance Analyzer window shown in Figure B.
Figure B illustrates the three types of analysis provided. Under Analysis Results, the analysis item marked with an exclamation point is a Recommendation, while those marked with a question mark are Suggestions. You can choose to have Access perform the optimizations listed as Suggestions or Recommendations. Those marked with light bulbs are Ideas for optimization, and if you choose to implement them, you will need to perform them manually. Clicking one of the items listed under Analysis Results displays more information about the benefits and tradeoffs of that optimization in the Analysis Notes section.
The Recommendation shown in Figure B indicates that an index file should be created. Because index files help reduce the number of disk accesses necessary to retrieve data during query execution, let's allow Access to create an index for this database. To do so, you would select the Recommendation as shown in Figure B, and then click the Optimize button. Access builds the index automatically.
Another design problem that can affect performance is incorrectly defined relationships between tables or no relationships defined at all. According to the Suggestions listed under the Analysis Results, there are problems with the relationships defined for this database. Again, you can let Access define these table relationships by selecting each Suggestion and then clicking the Optimize button.
The Ideas listed under the Analysis Results indicate that some data types are too large or inappropriate for the field data and should be changed. These optimizations, while helpful, have potential tradeoffs that are listed in the Analysis Notes. For example, when you click the Idea that stated that the data type of the Employee ID field in the Location And Position Table should be changed, the Analysis Notes cautions that “Changing a data type will erase existing data that doesn’t match the new data type and will limit the kind of information you can store in the field.” In this case, you will need to weigh the tradeoffs against the potential benefits before performing this optimization manually.