ACC2000: Defragment and Compact Database to Improve Performance

ID: Q209769

The information in this article applies to:

Microsoft Access 2000

Novice: Requires knowledge of the user interface on single-user computers.

SUMMARY

You can improve the performance of Microsoft Access if you periodically defragment your hard disk and compact your database.

MORE INFORMATION

Because the data on a hard disk will become fragmented over time, you should periodically run a disk-defragmentation utility (or defragmenter). If you make changes often within a database, portions of the database may also become fragmented. Therefore, you should also periodically run the Compact Database utility within Microsoft Access.

Defragmenting

A disk defragmenter will place all files, including the database file into contiguous clusters on a hard disk, making file access faster. With the exception of the Microsoft Windows NT operating system, if you do not defragment your hard disk, the operating system may have to go to several physical locations on the disk to retrieve the database file, making file access slower.

Compacting

Running the Compact Database utility within Microsoft Access can also improve the performance of the database. This utility makes a copy of the database file and, if it is fragmented, rearranges how the database file is stored on disk. When completed, the compacted database has reclaimed wasted space, and is usually smaller than the original. By compacting the database frequently, optimal performance of the database application is ensured, and page corruptions due to hardware problems, power failures/surges, and so on are resolved.

If a primary key exists in the table, compacting re-stores table records into their Primary Key order. This provides the equivalent of Non-maintained Clustered Indexes, and makes the read-ahead capabilities of the Microsoft Jet database engine much more efficient.

Compacting also updates the table statistics within the database that are used as Jet optimizes queries. These statistics can become outdated as data is added, manipulated, and deleted from the various tables. Query speed will be enhanced significantly, because they are now working with data that has been rewritten to the tables in contiguous pages. Scanning sequential pages is much faster than scanning fragmented pages. Queries are forced to recompile/optimize after each database compaction.

During compaction, you can use the original name for the compacted database file, or you can use a different name to create a separate file. If you use the same name and the database is compacted successfully, Microsoft Access automatically replaces the original file with the compacted version.

In Microsoft Access 2000, you can set an option to automate this procedure. To do so, on the Tools menu, click Options, click the General tab, and select (check) the box labeled Compact on Close. This will automatically compact and repair the database as it is closed.

Limitations of Compacting

For the Compact operation to succeed, you must have enough storage space on your hard disk for both the original and the compacted database.

You cannot compact an open database. In a multiuser environment, the compact operation fails if another user has the database open.

NOTE: In Microsoft Access 2000, you can compact a database while it is open, as long as the database has been opened exclusively.

Defragment or Compact First?

If you compact a database after running a defragmenter, you theoretically leave open disk space immediately after the .MDB file on the disk, allowing the operating system to place any additional information in the succeeding physical clusters. This would be very fast. However, if you defragment after running Compact Database, your .MDB may be placed on the first part of the disk followed by the rest of your files, with no open disk space until the end (the inside tracks) of the disk. This makes disk access somewhat slower.

REFERENCES

For more information about compacting databases, click Microsoft Access Help on the Help menu, type "Compact and Defragment" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information on Indexes, Clustered Indexes, and their types, see the Microsoft Jet Database Engine Programmer's Guide; Publisher, Microsoft Press.

Additional query words:

Keywords : GnlCmp
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbinfo