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

Chapter 30. Maintaining Your Application > Compacting Your Database

Compacting Your Database

As you and the users of your application work with a database, the database grows in size. 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, Access does not reclaim the disk space that the deleted objects occupied. This not only results in a very large database file, it also ultimately degrades performance, as the physical file becomes fragmented on disk. Compacting a database accomplishes the following 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 it executes queries, and marks all queries so that Jet recompiles them the next time they are run. These are two very important related benefits of the compacting process. If you have added indexes to a table, or the volume of data in the table has changed dramatically, the query won't execute efficiently. This is because Jet bases the stored query plan it uses to execute the query on inaccurate information. When you compact the database, Jet updates all table statistics and the plan for each query 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