Defragment and compact database to improve performance in Microsoft Access

You can surely improve the performance of MS Access if you defragment your hard disk regularly and compact your database. The data on a hard disk gets fragmented over the time therefore defragmentation over the time becomes imperative.

Defragmentation of MDB data

Disk Defragmenter places all files, including the database files into close clusters on hard disk which enables you to access files faster than usual. An exception to MS Windows NT operating system, if you do not defragment your hard disk, the operating system might have to go to several physical locations on the disk to recover database files thereby making the access slow.

Compacting

Running the Compact and Repair Database utility in MS Access database file can help you in improving the performance of database. This utility makes a copy of the database file and if it is a defragmented database, the utility rearranges the database file stored on disk. When the process completes, the compacted database reclaims wasted space which is smaller than the original. By compacting the database frequently, optimum performance of database application is ensured and page corruptions due to hardware problems, power failures or surges and so on are resolved.

If a primary key exists in the table, compacting restores table records into their primary key order. This offers the equivalent of non-maintained clustered indexes and makes the read-ahead capabilities of MS Jet database engine more efficient. Compacting updates table stats within the database that are used as Jet optimizes queries. This statistics become outdated when data is added or deleted from different tables.

Query speed increases significantly as these are now working with data that has been rewritten to the tables in neighboring pages. Scanning sequential pages is easier and fast than scanning fragmented pages. Therefore queries are compelled to recompile/optimize after each database compaction.

At the time of compaction, you can use the original name for the compacted database file or you can use a different name to create a different file. If you name it the same and the database gets compacted successfully, MS Access replaces the original file with the compacted version automatically.

Also, with MS Access user can set an option to automate this particular process. In order to do so, go to Tools menu, click Options > General tab > Compact on Close, this way you can automatically compact and repair the database as it is close.

Defragment or Compact first?

If you compact a database after defragmenting, you leave open disk space immediately after .MDB or .ACCDB on the disk, thereby allowing operating system to place additional information in the succeeding physical clusters. This will be real fast, however, if you defragment after running Compact Database, your MDB file may be placed on the first part of disk along with other files with no open disk space until the end (the inside tracks) of the disk. This makes disk access slower than before.

This way through defragmentation and compaction of database you can achieve improved performance in MS Access.

Article Resource : Access Recovery

Comments are closed.