compact database

[Nuno Gomes]

Hello,


What are the advantages or disadvantages to compact a database?





Thank's in advance,

Nuno Gomes

 

[bhicks11 via AccessMonster.com]

There are no disadvantages. It is good maintenance for your database, cleans
up things.

Here's a good discussion:

http://www.databasedev.co.uk/compacting-and-repairing-ms-access.html

Bonnie
http://www.dataplus-svc.com

 

[Chris O'C via AccessMonster.com]

Sounds like a homework question, but I'll tell you about the disadvantages
anyway.

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.

If there's no room on the disk immediately after the db file to allocate the
next data page (happens when there's another file stored adjacent to the db
file), the OS allocates another data block somewhere else on the disk. The
db file is now fragmented - or more fragmented than it was before the record
add or record update.

Fragmented db files are slower than unfragmented db files because the disk
read head has to travel to multiple places to read the data. If there's an
"accident," data recovery specialists might not recover all the file
fragments. A partial recovery is still an unopenable, corrupt db.

Chris
Microsoft MVP

 

[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.

 

[Chris O'C via AccessMonster.com]

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.

Chris
Microsoft MVP

After the db is compacted the next record add or record update (at least
the

[quoted text clipped - 4 lines]

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.

 

[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.

 

[bhicks11 via AccessMonster.com]

Yeah - interesting to me too!

Bonnie
http://www.dataplus-svc.com

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

[quoted text clipped - 29 lines]

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.

 

[sctech via AccessMonster.com]

very technical...but interesting...

Yeah - interesting to me too!

Bonnie
http://www.dataplus-svc.com
[quoted text clipped - 6 lines]

Regards,
Keith.