compact database

[Nuno Gomes]

Hello,


What are the advantages or disadvantages to compact a database?





Thank's in advance,

Nuno Gomes

 

[Keith Wilby]

After the db is compacted the next record add or record update (at least
the
ones that are bigger than the original record) will require extra time
while
Jet (or ACE) adds another data page, moves half the records from the
current
data page to the new data page and updates all the pointers to each of the
records in these data pages and updates the table's indexes.

Is this documented anywhere? I've never come across the phrase "data page"
in this context and I don't particularly understand what you've posted.

Thanks.

Keith.

 

[Keith Wilby]

Jet stores the data in 4KB data pages. When it compacts the db, all the
records of a table are consolidated into the smallest number of data
pages.
All pages for that table are full of records except the last one. (Well
the
last page might be full, but that's rare.) Now add a new record. Where
is
Jet going to squeeze it in? In the middle and push every record after it
up
by one? No. It splits the page before adding the record. For a good
explanation of how this happens (using SQL Server as an example) see this
page:

http://www.informit.com/library/content.aspx?b=STY_Sql_Server_7&seqNum=144

"When a SQL Server page is full and another row must be put on that page,
a
page split will occur. A new page will be assigned to the index or table,
and 50 percent of the rows will be moved to the new page. Then the new row
will be added in the appropriate location. As you might imagine, this can
be
an expensive operation if it happens frequently."

After a compaction (and for some time thereafter until the data pages are
sufficiently fragmented), every new record added that isn't at the end of
the
table requires a page split to insert the new record. Same happens when
Jet
has to make room for an update that doesn't fit in the original record's
space. Another page split and records moved over.

All that maneuvering of records takes additional time to make an insert or
update.

Thank you Chris. Just goes to show there's always something new to learn.

Regards,
Keith.