MS-Access / Getting Started

---

Improving Absolute Speed

When discussing an application's performance, the word performance is usually synonymous with speed. You'll find two types of speed in software development:

• Absolute: Absolute speed is how quickly your application performs a function, such as running a certain query. Absolute speed can be measured in units of time.
• Perceived: Perceived speed is how end users perceive an application's performance. This phenomenon of perceived speed is often the result of visual feedback provided to the user while the application performs a task. Whereas absolute speed can be measured, perceived speed is very subjective. (For more on perceived speed, see the "Improving Perceived Speed" section, later in this tutorial.)

Among the most important steps for increasing absolute speed are the following:

• Keeping your application in a compiled state: As was discussed in the "Distributing .accde Files" section, converting your .accdb file to an .accde file is a good way to ensure that the code is always in a compiled state.
• Organizing your procedures into "smart" modules: As was discussed in the "Understanding Module Load on Demand" section, separating procedures into modules based on how frequently they'll be used is a good approach.
• Opening databases exclusively: You should always open a database exclusively in a single-user environment. If your database is a standalone application (meaning that nothing is shared over a network), opening the database in exclusive mode really boosts performance. If the database runs on a network and is shared by multiple users, the database can't be opened exclusively. (Actually, the first user can open it exclusively, but no other user can access the database until the first user closes it.) The preferred method for running an application in a network environment is to run Access and the main .accdb file locally, and then link to a shared database containing the data on the server.

To open a database exclusively in Access, in the Open dialog box click the Open button down arrow and select Open Exclusive.

Tip: If you always want to open a database exclusively, you can make this the default setting. Click the Microsoft Office button and select Access Options. In the Access Options dialog box, select the Advanced tab. Change the Default Open Mode to Exclusive if you always want to open a database exclusively. (The Default Open Mode is otherwise set to Shared.)

• Compacting databases regularly: An often-overlooked way of maximizing a database's performance is to routinely compact the database. When records are deleted from an Access database, the hard drive space that held the deleted data is not recovered until a compact is performed. In addition, a database becomes fragmented as data is modified in the database. Compacting a database defragments the database and recovers hard drive space.

Note: Although databases should be compacted regularly, that does not mean they should be compacted every time they're used. Using the Compact on Close option.

All the preceding methods are excellent (and necessary) ways to help keep your applications running at their optimum performance level, but these aren't the only tasks that you can perform to increase the absolute speed of your application. Almost every area of development, from forms to modules, can be optimized to give your application maximum absolute speed.

Tuning your system

One important aspect of performance has nothing to do with the actual application design - that is, the computer on which the application is running. Even though it's impossible to account for all the various configurations your clients may have, you can do some things for your computer and recommend that end users do them for theirs:

• Equip the computer with as much memory as possible. This step often becomes an issue related to the cost of purchasing and installing the computer memory. However, as memory prices continue to decrease, one of the most effective methods of increasing the speed of Access applications is to add additional memory to the user's computer.
• Close all applications that aren't being used. Windows makes it very handy to keep as many applications loaded as you want - on the odd chance that you may need to use one of them. Although Windows XP, Windows Vista, and Windows 7 are very good at handling memory for multiple applications, each application still uses computer resources.
• Make sure that your Windows swap file is on a fast drive with plenty of free space. If possible, you should also set the minimum hard drive space available for virtual memory to at least twice the physical RAM installed and make it a permanent swap file.
• Defragment the hard drive often. Defragmenting a hard drive allows data to be retrieved in larger sections, thus causing fewer reads and less repositioning of the read heads.