Improve performance of an Access database

You can use the Performance Analyzer to optimize the performance of a Microsoft Access database. The Performance Analyzer is not available in an Access project.

1. Open the Access database you want to optimize.
2. On the Tools menu, point to Analyze, and then click Performance.
3. Click the tab for the type of database object that you want to optimize. Click the All Object Types tab to view a list of all database objects at once.
4. Select the names of the database objects that you want to optimize. Click Select All to select all database objects in the list.
5. Repeat steps 3 and 4 until you've selected all the objects you want to optimize, and then click OK.

   The Performance Analyzer lists three kinds of analysis results: Recommendation, Suggestion, and Idea. When you click an item in the Analysis Results list, information about the proposed optimization is displayed in the Analysis Notes box below the list. Suggestion optimizations have potential tradeoffs that you should consider before performing them. To view a description of the trade-offs, click a Suggestion in the list and then read the information in the Analysis Notes box. Access can perform Recommendation and Suggestion optimizations for you. You must perform Idea optimizations yourself.

6. Click one or more of the Recommendation or Suggestion optimizations you want performed, and then click Optimize. The Performance Analyzer will perform the optimizations and then mark them as Fixed. Continue this process until the Performance Analyzer has completed all Recommendations and Suggestions that you want it to perform.

   To perform all Recommendation and Suggestion optimizations, click Select All, and then click Optimize.

   To perform an Idea optimization, click the optimization, and then follow the instructions displayed in the Analysis Notes box.

Note  The Performance Analyzer doesn't provide suggestions on how to improve the performance of Microsoft Access itself or the system it is running on.

The following guidelines can help you optimize the performance of Microsoft Access on your computer:

• If you're using databases that other users don't need to share, install Microsoft Access and all your databases on your hard disk drive rather than on a network server.
• If you are the only person using a database, open the database for exclusive use; in the Open dialog box, click the arrow next to Open button, and then click Open Exclusive.
• Make more memory available by closing applications that you aren't using.
• Increase RAM on your computer. 40 MB of memory is recommended— 32 MB of memory plus an additional 8 MB of memory for Microsoft Access.
• Don't use any of your RAM for a RAM disk.
• Periodically delete unnecessary files and empty your Recycle Bin, compact your databases, and then defragment your hard disk with the Microsoft Windows Disk Defragmenter. To run the Disk Defragmenter, click the Windows Start button, point to Programs, point to Accessories, point to System Tools, and then click Disk Defragmenter.
• In most cases, the default virtual memory setting used by Windows should perform optimally. However, in some situations, adjusting virtual memory parameters can improve performance. If you've already tried deleting unnecessary files and you still have a performance problem, try changing the default virtual memory setting in the following cases:
  • You don't have much disk space available on the drive that is currently being used for virtual memory, and another local drive with space is available.
  • Another local drive is available that is faster than the current drive (unless that disk is heavily used).

  In these cases, you might get better performance by specifying a different drive for virtual memory.

  You also might get better performance by specifying that the disk space available for virtual memory be at least 25 MB minus available RAM. For example, if your computer has 12 MB of RAM, you should specify at least 13 MB of virtual memory. You may want to specify more if you are running several large applications.

  To change Windows virtual memory parameters, in Windows Control Panel, double-click the System icon, click Performance Options under the Advanced tab, click the Change button in the Virtual Memory section, and then specify a different hard disk, or enter a value in the Initial size box that is at least 25 minus your available RAM.

• If you have a wallpaper (full-screen background) bitmap on your Windows desktop, replace it with a solid color or pattern bitmap, or no bitmap at all.
• If you use a screen saver, use a blank screen saver or consider not using one at all.
• Eliminate disk compression software, or consider moving your databases to an uncompressed drive.
• To ensure optimal performance, use substitution fonts only if you need dual-font support to display all of your data.

• Use the Performance Analyzer to analyze specific tables in your database.

• 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; this can save space in your database and improve join operations. 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 by 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.

Note  You can link a table only in a Microsoft Access database, not a Microsoft Access project.

Although you can use linked tables as if they were regular tables in your Microsoft Access database, it's important to keep in mind that they aren't actually in your Access database. Each time you view data 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.

If you're using a linked table on a network or in an SQL database, follow these guidelines for best results:

• You can greatly enhance performance when opening the main database and opening tables and forms by forcing the linked database to remain open. To do this, create an empty table in the linked database, and link the table in the main database. Then use the OpenRecordset method to open the linked table. This prevents the Microsoft Jet database engine from repeatedly opening and closing the linked database and creating and deleting the associated .ldb file.
• View only the data that you need. Don't move up and down the page unnecessarily in the datasheet. Avoid jumping to the last record in a large table. If you want to add new records to a large table, use the Data Entry command on the Records menu to avoid loading existing records into memory.
• Use filters or queries to limit the number of records that you view in a form or datasheet. This way, Microsoft Access can transfer less data over the network.
• In queries that involve linked tables, avoid using functions in query criteria. In particular, avoid using domain aggregate functions, such as Dsum, anywhere in your queries. When you use a domain aggregate function, Microsoft Access retrieves all of the data in the linked table to execute the query.
• If you often add records to a linked table, create a form for adding records that has the DataEntry property set to Yes. When you open the form to enter new data, Microsoft Access doesn't display any existing records. This property setting saves time because Microsoft Access doesn't have to retrieve the records in the linked table.
• Remember that other users might be trying to use an external table at the same time you are. When a Microsoft Access database is on a network, avoid locking records longer than necessary.

If the data in your front-end/back-end application consists only of Microsoft SQL Server tables, you can use either a Microsoft Access project or a Microsoft Access database as the front end of your application. However, by using an Access project, you will benefit from the advantages of projects such as a larger number of tables and larger database size. If you want the data in your front-end/back-end application to consist of both Microsoft Access tables and Microsoft SQL server tables, you must use an Access database as the front end.

If you're connecting to an external SQL database table, you can achieve the best performance results by linking to the SQL tables instead of opening the tables directly. You can open external SQL tables directly only by using Microsoft Visual Basic code. Linked tables are considerably faster, more powerful, and more efficient than directly opened tables.

Additional performance tips

• Retrieve only the data you need. Design your queries to limit the number of records that you retrieve, and select only the fields you need, so that Microsoft Access can transfer as little data as possible over the network.
• Don't use updatable result sets (dynasets) if you're not retrieving many records, are not updating the data, and don't need to see changes made by other users. To prevent result sets from being updated in a form, set the form's RecordSetType property to Snapshot. Snapshots are faster to open and scroll through than dynasets.
• If you need to retrieve a large number of records, a dynaset is faster and more efficient than a snapshot. For example, moving to the end of a snapshot requires the entire result set to be downloaded to the local computer, but with a dynaset only the last screen of data is downloaded to the local computer. In addition, the fastest way to add new records to a table, form, or query is to click Data Entry on the Records menu. (Data Entry isn't available if the RecordsetType property is set to Snapshot.)
• Use cache memory. If you will reuse the most recent data from the server while the application is running, it's faster to retrieve a single large chunk of data (many rows) and store it in a cache than it is to retrieve many individual rows. Microsoft Access forms and datasheets automatically use a cache. If you are retrieving data by using a Recordset object created in Microsoft Visual Basic, you can use the CacheSize property to specify how many records to retrieve at one time into local memory.
• For bulk update queries against ODBC data sources, optimize performance on the server by setting the FailOnError property to Yes.
• Avoid using queries that cause processing to be done on a local client computer. When accessing external data, the Jet database engine processes data locally only when the operation can't be performed by the external database server. Query operations performed locally (as defined by the SQL commands used to implement them) include:
  • JOIN operations between tables from different remote data sources. (For example, assume that the join involves a local table or query with few records and a remote table with many more records, and the remote table's join field is indexed. In this case, Access returns only the records that match the local table or query, thus greatly improving query performance.)
  • JOIN operations based on a query with the DISTINCT predicate or a GROUP BY clause.
  • Outer joins containing syntax not supported by the server.
  • DISTINCT predicates containing operations that can't be processed remotely.
  • The LIKE operator used with Text or Memo fields (may not be supported by some servers).
  • Multiple-level GROUP BY arguments and totals, such as those used in reports with multiple grouping levels.
  • GROUP BY arguments based on a query with a DISTINCT predicate or a GROUP BY clause.
  • Crosstab queries that have more than one aggregate, that have field, row, or column headings that contain aggregates, or that have a user-defined ORDER BY clause.
  • TOP n or TOP n PERCENT predicates.
  • User-defined functions, or operators or functions that aren't supported by the server.
  • Complex combinations of INNER JOIN, LEFT JOIN, or RIGHT JOIN operations in nested queries.

The following guidelines can help you optimize the performance of Microsoft Access databases that are used in a multiuser environment.

• 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. You can separate the tables from other database objects by using the Database Splitter Wizard.
• Choose a suitable record-locking strategy. If you use optimistic locking (No Locks), Access doesn't lock a record or page until a user saves changes to it, and data might be more readily available as a result. If you use pessimistic locking (Edited Records), Access locks the record as soon as a user begins to edit it— - records might be locked for longer periods of time, but users don't have to decide whose changes to save if two users edit the same record simultaneously.
• Choose the appropriate locking level strategy (page-level or record-level locking).
• Avoid locking conflicts by adjusting Refresh Interval, Update Retry Interval, Number Of Update Retries, and ODBC Refresh Interval settings (if applicable).
• Optimize the performance of tables (including tables linked to other data sources such as Microsoft SQL Server).
• Convert the workgroup information file from Access 97 or earlier to the current version of Access.

If the lists in fields in the Filter By Form window take too long to display or they aren't displaying values from the underlying table, you can change Filter By Form performance for all tables, queries, and forms, or change performance for a specific form. In either case, you can prevent the lists from displaying the underlying table's field values, display field values on the list for certain types of indexed or nonindexed fields only, or change the record limit that determines if the list displays a field's values.

An index helps Microsoft Access find and sort records faster. Access uses indexes in a table as you use an index in a book: to find data, it looks up the location of the data in the index. You can create indexes based on a single field or on multiple fields. Multiple-field indexes enable you to distinguish between records in which the first field may have the same value.

Deciding which fields to index

You'll probably want to index fields you search frequently, fields you sort, or fields that you join to fields in other tables in queries. However, indexes can slow down some action queries such as append queries, when the indexes for many fields need to be updated while performing these operations.

The primary key of a table is automatically indexed, and you can't index a field whose data type is OLE Object. For other fields, you should consider indexing a field if all the following apply:

• The field's data type is Text, Number, Currency, or Date/Time.
• You anticipate searching for values stored in the field.
• You anticipate sorting values in the field.
• You anticipate storing many different values in the field. If many of the values in the field are the same, the index may not significantly speed up queries.

Multiple-field indexes

If you think you'll often search or sort by two or more fields at a time, you can create an index for that combination of fields. For example, if you often set criteria for LastName and FirstName fields in the same query, it makes sense to create a multiple-field index on both fields.

When you sort a table by a multiple-field index, Microsoft Access sorts first by the first field defined for the index. If there are records with duplicate values in the first field, Microsoft Access sorts next by the second field defined for the index, and so on.

You can include up to 10 fields in a multiple-field index.

There are several things you can do to make your forms run faster.

There are several things you can do to make your list boxes, combo boxes, and drop-down list boxes run faster:

• Base the list box, combo box, or drop-down list box on a saved query instead of an SQL statement. On a form, if you use a wizard to create a list box or combo box, Microsoft Access automatically sets the RowSource property of the control to an SQL statement. To change the RowSource property to a saved query, click the Build button next to the RowSource property box. With the Query Builder window open, click Save on the File menu, and type a name for the query. When you close the Query Builder window, click Yes when Microsoft Access asks if you want to update the property.
• Base the drop-down list box on a saved query instead of a table. On a data access page, if you use a wizard to create a drop-down list box, Access automatically sets the ListRowSource property of the control to the table you specified in the wizard. To retrieve rows from a query instead of a table, select the query name from the ListRowSource property box.
• In the query specified in the RowSource or ListRowSource property box, include only fields that are absolutely necessary. Extra fields can decrease performance.
• Index both the first field displayed in the combo box, list box, or drop-down list box and the bound field (if the fields are different).
• In combo boxes on a form, set the AutoExpand property to No if you don't need the fill-in-as-you-type feature.
• On a form, if the AutoExpand property for a combo box is set to Yes, the first displayed field should have a Text data type instead of a Number data type. In order to find a match in the list, Microsoft Access converts a numeric value to text. If the data type is Text, Microsoft Access doesn't have to do this conversion.
• On a form, if the bound field in a lookup combo box is not the displayed field:
  • Don't use expressions for the bound field or the displayed field.
  • Don't use restrictions in the row source.
  • Use single-table (or query) row sources rather than multiple-table row sources, if possible.
• Don't create list boxes, combo boxes or drop-down list boxes based on data in a linked table if the data won't change. It's better to import the data into your database in this case.

Note  The Query Builder is not available in a data access page. Wizards are not available in a stand-alone data access page, or when you point a data access page to a database other than the one currently open. In both cases, you will have to create a list box or drop-down list box on your own.

There are several things you can do to make your reports print faster.

You can do several things to make your data access pages load faster in Page view or in Microsoft Internet Explorer 5.01 with Service Pack 2 (SP2) or later.

If your database contains Microsoft Visual Basic for Applications (VBA) code, saving your Microsoft Access database as an MDE file compiles all modules, removes all editable source code, and compacts the destination database. Your Visual Basic code will continue to run, but it cannot be viewed or edited.

Security  Saving your database as an MDE file helps protect your forms and reports without requiring users to log on or requiring you to create and manage the user accounts and permissions that are needed for user-level security.

Your database will continue to function normally— you can still update data and run reports. Additionally, the size of the database will be reduced due to the removal of the code, so memory usage is optimized, which will improve performance.

Saving your Access database as an MDE file prevents the following actions:

• Viewing, modifying, or creating forms, reports, or modules in Design view.
• Adding, deleting, or changing references to object libraries or databases.
• Changing code— an MDE file contains no source code.
• Importing or exporting forms, reports, or modules. However, tables, queries, data access pages, and macros can be imported from or exported to non-MDE databases.

Microsoft Access combines compacting and repairing an Access file into a single process.