Fine-Tuning with Performance Analyzer

Even the most experienced database designers (including us) don’t always take advantage of all the techniques available to improve performance in an Access application. Fortunately, Access provides Performance Analyzer to help you do a final analysis after you build most of your application. In this section, we’ll let Performance Analyzer analyze the Housing Reservations sample database (Housing.accdb). To start Performance Analyzer, click the Analyze Performance button in the Analyze group on the Database Tools tab. Access opens the dialog box shown in Figure 24–7.

Figure 24–7: You can select which objects to analyze from the eight tabs of Performance Analyzer.

You can select a specific category of objects to analyze-Current Database (which lets you analyze table relationships), Tables, Queries, Forms, Reports, Macros, Modules, or All Object Types. Within a category, you can select the check box next to an object name to select it for analysis. You can click the Select All button to ask Performance Analyzer to examine all objects or click Deselect All if you made a mistake and want to start again. In this example, we chose the All Object Types tab, clicked Select All, and then clicked sample tables, queries, forms, and reports that aren’t part of the actual application (all the extra examples we built for the book) to deselect them. (In the Housing Reservations database, select all tables that have names beginning with “tbl” or “tlkp,” and select all queries, forms, and reports except those that have names beginning with the letter z or that have “Example,” “Xmpl,” or “USys” as part of the object name.)

Click OK to run Performance Analyzer. Performance Analyzer opens a dialog box that shows you its progress as it analyzes the objects you selected. When it is finished, Performance Analyzer displays the results of its analysis, similar to those shown in Figure 24–8.

Figure 24–8: Performance Analyzer displays recommendations to improve your application.

You can scan the list of recommendations, suggestions, and ideas displayed by Performance Analyzer. (Notice the key below the Analysis Results list.) Click any recommendation or suggestion that you like, and then click the Optimize button to have Performance Analyzer implement the change on the spot. After Performance Analyzer implements a change, you’ll see a check mark next to the item. If you like, you can click the Select All button to highlight all the recommendations and suggestions and then click Optimize to implement the fixes.

Personally, we’d rather choose the ones we want one at a time. For example, the idea to change the PostalCode field from text to long integer won’t work if you’re storing Canadian or European postal codes in your database. Also, we know that tblReservations is already related to tblFacilities through the tblFacilityRooms table, so adding a direct relationship between tblFacilties and tblReservations would be redundant.

Although you can implement recommendations and suggestions directly from Performance Analyzer, you can’t do so with ideas. Most ideas are changes that could potentially cause a lot of additional work. For example, changing a data type of a field in a table might improve performance slightly, but it might also cause problems in dozens of queries, forms, and reports that you’ve already built using that table field. Other ideas are fixes that Performance Analyzer isn’t certain will help; they depend on how you designed your application. We recommend that you look at the recommendations and suggestions and implement the ones that make the most sense for your application.