• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Compacting Your Database

As you and the users of your application work with a database, the database grows in size. In order to maintain a high state of performance, Access defers the removal of discarded pages from the database until you explicitly compact the database file. This means that, as you add data and other objects to the database and remove data and objects from the database, the disk space that was occupied by the deleted objects is not reclaimed. This results not only in a very large database (MDB) file, but it also ultimately means a degradation in performance as the physical file becomes fragmented on disk. Compacting a database accomplishes these tasks:

  • Reclaims all space occupied by deleted data and database objects.

  • Reorganizes the database file so that the pages of each table in the database are contiguous. This improves performance because, as the user works with the table, the data in the table is located contiguously on the disk.

  • Resets counter fields so that the next value will be one more than the last undeleted counter value. If, while testing, you add many records that you delete just prior to placing the application in production, compacting the database resets all the counter values back to 1.

  • Re-creates the table statistics used by the Jet Engine when queries are executed and marks all queries to be recompiled the next time they are run. These are two very important related benefits of the compacting process. If indexes have been added to a table or the volume of data in the table has been dramatically changed, the query won't execute efficiently. This is because the stored query plan that Jet uses to execute the query is based on inaccurate information. When the database is compacted, all table statistics and the query plan for each query are updated to reflect the current state of the tables in the database.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint