Hello,


What are the advantages or disadvantages to compact a database?





Thank's in advance,

Nuno Gomes

"Chris O'C via AccessMonster.com" <u29189@uwe> wrote in message
news:87f37b6d1182f@uwe...
>
> 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" <u29189@uwe> wrote in message
news:88006adb6a23d@uwe...
> 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/cont...r_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.