Share via

Improving Access Database Performance

  • Article

One of the areas where I see a lot of questions in the newsgroups concerns the performance of Access databases. Access databases are used for a variety of applications; everything from relatively small to medium records management to proof of concept databases. For those of you who are new to Access as well as veterans to database creation and management, you will most likely beome more and more concerned with database performance as your databases grow. Follows are a few simple tips that can make a big difference in the performance of your Access databases. These tips apply to both .mdbs and adps:

 * One of the easiest ways to increase performance in your databases is to regularly compact your database. As you work with your database doing such things as creating and deleting new tables and forms/reports, the physical file on the hard drive becomes fragmented just as your hard drive is fragmented when you add and remove files with your operating system. As defragmentation progressly becomes worse over time, so the preformance of your database also becomes poorer. As compacting, performance increases because Access can more easily find the objects it needs and load them into memory. Defragmenting the database has the additional benefit of making the database smaller. Compacting is as easy as clicking Database Utilities on the Tools menu and then clicking Compact and Repair Database. I would also recommend making a copy of your database before compacting it in case something goes wrong during the operation.

 * From the onset, normalize your database and keep it that way. Normalization is the process of simplifying the design of a database so that it achieves an optimum structure. There are a series of rules that you can follow to normalize a database. Poorly designed databases cause you to create complex and convoluted queries in order to retrieve the data you want. Since queries are an integral part of a database, loss of performance there affects the entire application. There are several references available to guide you through the process to include Database Design for Mere Mortals by Mike Hernandez, Handbook of Relational Database Design by Barbara von Halle and Candace Fleming, and Access 2002 Desktop Developer's Handbook by Paul Litwin, Ken Getz, and Mike Gunderloy.

 * Retrict the number of records returned by your queries. Imagine the time spent as your queries load into memory and display all of the rows from a table or a series of tables. The performace of vitually any query can be improved by returning fewer records. This is even more important when the query is used as the record source for a form. You can restrict the number of records returned by applying criteria in the Query Grid when in Design view of a query. As criteria is applied to the record source from left-to-right in the Criteria row,it is most effective to add criteria that eliminates the most rows first.

These are just a few of the tips that can be used to optimum you databases. For more, see the MSPress books available on Access as well as those books from other publishers such as Sybex, SAMs and so forth.

Additional information from Tony Toews:

There are quite a number of other things you can do for performance as well.

Some common performance problems in Access 2000 and newer are:

- LDB locking with a persistent recordset connection or an always open bound form (multiple users)

- sub datasheet Name property set to [Auto] should be [None]

- Track name AutoCorrect should be off

For more information on these, less likely causes, other tips and links to MS KB articles visit my Access Performance FAQ page at https://www.granite.ab.ca/access/performancefaq.htm

Comments

  • Anonymous
    June 08, 2004
    There are quite a number of other things you can do for performance as well.

    Some common performance problems in Access 2000 and newer are:
    - LDB locking which a persistent recordset connection or an always open bound form corrects (multiple users)
    - sub datasheet Name property set to [Auto] should be [None]
    - Track name AutoCorrect should be off

    For more information on these, less likely causes, other tips and links to MS KB articles visit my Access Performance FAQ page at http://www.granite.ab.ca/access/performancefaq.htm
  • Anonymous
    July 14, 2004
    Thanks!
  • Anonymous
    July 20, 2004
    Thanks a lot! My database was so big and slow - I didn't know about the compact and repair funtion!