TechTips: Why does the file keep on growing? 
Author Message
 TechTips: Why does the file keep on growing?

When you delete 100,000 records from a table, you'd expect it to get a little
smaller, right?

Wrong!

A database table or database file is a self-maintaining structure, which does
not shrink in size (as seen by DOS) until you "compact" it.  (And this, as you
may have discovered, is basically a file-copy operation where the data is
copied to a new file and the old one is then deleted.)

SWEET FREEDOM:
In the case of Paradox and Access, deleted records are placed onto a "free
list" of space to be recycled.  This space is gradually consumed before the
file is again extended -- although various versions of Access do extend the
file sometimes even when there is free-space available elsewhere.  

xBase systems are unfortunately worse.  They don't really delete records; they
simply hide them from view.  The space is not recycled until the table is
compacted.  Fast, yes, but not space-efficient.

"YES WE WILL ALL.. GO DOWN.. TOGETHER..."
It is important to note that all of these database systems actually work with
disk-space in a unit *larger* than an individual record:  a "block" or "page."
Typical block sizes are from 2K to 32K in some multiple of a kilobyte.  One
or more records are stored in each block, and there can be free-space *within*
a block that may or may not be usable by a particular operation.  (The DBMS
tries to be fast, even at the expense of space-efficiency.)

If a block contains any record at all, then it is not "free" and therefore
cannot be recycled.  It is possible, therefore, to have a database with a
whole lot of "almost empty" blocks.  The only solution to this condition is to
compact the database or table.

This "blocking" behavior can produce some unexpected space-related side
effects.  An integral number of records must fit within a single block; they
can't span more than one block.  Paradox stores the same number of records per
block, but Access stores *variable-length* records and can squeeze a variable
number of records in one block.  This causes Access to have a challenge that
Paradox doesn't have:  what to do when a record suddenly grows larger.  It
appears that the block is unconditionally "split" and new space allocation
occurs at that time -- but only Microsoft knows for sure.

Anyhow, you can never exactly predict, nor control, exactly how records will
be distributed and re-distributed among the physical blocks as time goes by.  
Fortunately, you don't have to worry about it much.

SQUEEZE IN A LITTLE TIGHTER, PLEASE:
Ultimately, the only way to make the file shrink is to compact it.

"Compacting" consists of copying all the non-deleted records into a new
database or table, generally in primary-key order, placing as many records in
each block as will fit there.  As you can imagine, this is a time-consuming
operation that requires at least as much free disk-space as the existing file
occupies.

"Compacting," in the case of Access, also removes "temporary objects."  These
are tables that Access creates for its own purposes and conceals from your
view.  If you ever wondered where the results of queries actually go while
you're viewing them... there's your answer.  Various versions of Access also
are known to let lots of "temporary objects" pile up, making frequent
compaction more necessary especially during development.

DOES IT HURT ENOUGH YET?
How frequently should you compact a database?  Our recommendation is, "only
when you have to, and ALWAYS repair/verify the table or database before you do
so."  To put it another way, "only apply a painkiller when the pain becomes a
killer."

Let me say it again:  (1) compacting does not need to be performed all the
time; and (2) compacting should be performed only for a good reason.

"Improved speed," by itself, is a side-effect of compacting the database but
not a sufficient reason (by itself) to do so.  The speed of a well-designed,
freshly compacted database will be better than a well-designed database that
has not been compacted in a while, but if the database is well designed the
speed will continue to be acceptable.

If you have lots of disk space, it's a good idea to simply leave the table or
database to its own business.  Even if you have deleted lots of records, that
does not mean that you -must- compact the database.  You can safely allow the
structure to maintain itself, growing however large it happens to be, and go
for weeks or months without repair/compact.  That is, after all, what the
design was intended to allow.

SAFETY FIRST:
You should *always* perform table verification (Paradox "table repair" or
equivalent; Access "repair database") before you do any compacting.  The
compaction processes generally assume, perhaps more than they ought to, that
the table or database being compacted is in fine shape at the time.  You know
also that they're going to perform a complete sweep through the table.  If
something is wrong, then they're going to find it -- and the outcome might not
be very pleasant.

Even if you do not compact the database, you should nonetheless verify it on a
regular basis.  For example, it should be a regular part of your system
maintenance plan to test the integrity of your entire database, or all of the
tables, at least before the weekly full-backups are performed.  (Umm... you
DO, of course... oh, of course you do.  Daily?  And you check to be sure it
worked right, right?  Oh, terrific!)

---
? Mike Robinson; Sundial Services, Scottsdale AZ

http://www.*-*-*.com/
"Specialists in PC and Macintosh database systems since 1992."  (And pretty
clever tool-writers too; check our page.)



Wed, 18 Jun 1902 08:00:00 GMT  
 TechTips: Why does the file keep on growing?


Quote:
>This "blocking" behavior can produce some unexpected space-related side
>effects.  An integral number of records must fit within a single block; they
>can't span more than one block.  Paradox stores the same number of records per
>block, but Access stores *variable-length* records and can squeeze a variable
>number of records in one block.  This causes Access to have a challenge that
>Paradox doesn't have:  what to do when a record suddenly grows larger.  It
>appears that the block is unconditionally "split" and new space allocation
>occurs at that time -- but only Microsoft knows for sure.

Another relevant point, which fell out during final-edit of this article, is
the effect of keys.  Paradox for Windows, in particular, always stores records
in the blocks in ascending primary-key order (if there is one), so it may
split records in interesting ways.  This can produce more-empty or less-empty
blocks, according to a strategy that is certainly controlled and well-defined
but which does sometimes lead to unexpected space-related effects.

Again, the "key" point is that storage-allocation behavior sometimes just *is*
a little bit counter-intuitive.  But it is all "under control."



Wed, 18 Jun 1902 08:00:00 GMT  
 TechTips: Why does the file keep on growing?

Quote:

> When you delete 100,000 records from a table, you'd expect it to get a little
> smaller, right?

> Wrong!

> A database table or database file is a self-maintaining structure, which does
> not shrink in size (as seen by DOS) until you "compact" it.  (And this, as you
> may have discovered, is basically a file-copy operation where the data is
> copied to a new file and the old one is then deleted.)

> SWEET FREEDOM:
> In the case of Paradox and Access, deleted records are placed onto a "free
> list" of space to be recycled.  This space is gradually consumed before the
> file is again extended -- although various versions of Access do extend the
> file sometimes even when there is free-space available elsewhere.

> xBase systems are unfortunately worse.  They don't really delete records; they
> simply hide them from view.  The space is not recycled until the table is
> compacted.  Fast, yes, but not space-efficient.

> "YES WE WILL ALL.. GO DOWN.. TOGETHER..."
> It is important to note that all of these database systems actually work with
> disk-space in a unit *larger* than an individual record:  a "block" or "page."
> Typical block sizes are from 2K to 32K in some multiple of a kilobyte.  One
> or more records are stored in each block, and there can be free-space *within*
> a block that may or may not be usable by a particular operation.  (The DBMS
> tries to be fast, even at the expense of space-efficiency.)

> If a block contains any record at all, then it is not "free" and therefore
> cannot be recycled.  It is possible, therefore, to have a database with a
> whole lot of "almost empty" blocks.  The only solution to this condition is to
> compact the database or table.

> This "blocking" behavior can produce some unexpected space-related side
> effects.  An integral number of records must fit within a single block; they
> can't span more than one block.  Paradox stores the same number of records per
> block, but Access stores *variable-length* records and can squeeze a variable
> number of records in one block.  This causes Access to have a challenge that
> Paradox doesn't have:  what to do when a record suddenly grows larger.  It
> appears that the block is unconditionally "split" and new space allocation
> occurs at that time -- but only Microsoft knows for sure.

> Anyhow, you can never exactly predict, nor control, exactly how records will
> be distributed and re-distributed among the physical blocks as time goes by.
> Fortunately, you don't have to worry about it much.

> SQUEEZE IN A LITTLE TIGHTER, PLEASE:
> Ultimately, the only way to make the file shrink is to compact it.

> "Compacting" consists of copying all the non-deleted records into a new
> database or table, generally in primary-key order, placing as many records in
> each block as will fit there.  As you can imagine, this is a time-consuming
> operation that requires at least as much free disk-space as the existing file
> occupies.

> "Compacting," in the case of Access, also removes "temporary objects."  These
> are tables that Access creates for its own purposes and conceals from your
> view.  If you ever wondered where the results of queries actually go while
> you're viewing them... there's your answer.  Various versions of Access also
> are known to let lots of "temporary objects" pile up, making frequent
> compaction more necessary especially during development.

> DOES IT HURT ENOUGH YET?
> How frequently should you compact a database?  Our recommendation is, "only
> when you have to, and ALWAYS repair/verify the table or database before you do
> so."  To put it another way, "only apply a painkiller when the pain becomes a
> killer."

> Let me say it again:  (1) compacting does not need to be performed all the
> time; and (2) compacting should be performed only for a good reason.

> "Improved speed," by itself, is a side-effect of compacting the database but
> not a sufficient reason (by itself) to do so.  The speed of a well-designed,
> freshly compacted database will be better than a well-designed database that
> has not been compacted in a while, but if the database is well designed the
> speed will continue to be acceptable.

> If you have lots of disk space, it's a good idea to simply leave the table or
> database to its own business.  Even if you have deleted lots of records, that
> does not mean that you -must- compact the database.  You can safely allow the
> structure to maintain itself, growing however large it happens to be, and go
> for weeks or months without repair/compact.  That is, after all, what the
> design was intended to allow.

> SAFETY FIRST:
> You should *always* perform table verification (Paradox "table repair" or
> equivalent; Access "repair database") before you do any compacting.  The
> compaction processes generally assume, perhaps more than they ought to, that
> the table or database being compacted is in fine shape at the time.  You know
> also that they're going to perform a complete sweep through the table.  If
> something is wrong, then they're going to find it -- and the outcome might not
> be very pleasant.

> Even if you do not compact the database, you should nonetheless verify it on a
> regular basis.  For example, it should be a regular part of your system
> maintenance plan to test the integrity of your entire database, or all of the
> tables, at least before the weekly full-backups are performed.  (Umm... you
> DO, of course... oh, of course you do.  Daily?  And you check to be sure it
> worked right, right?  Oh, terrific!)

> ---
> ? Mike Robinson; Sundial Services, Scottsdale AZ

> http://www.sundialservices.com/
> "Specialists in PC and Macintosh database systems since 1992."  (And pretty
> clever tool-writers too; check our page.)

Seems to me you are doing this the hard way.  If you're using Paradox 7,
why not go into restructure for the table, select "Pack Table" and save
the table.  Paradox says it will work!


Wed, 18 Jun 1902 08:00:00 GMT  
 TechTips: Why does the file keep on growing?

there are two ways to set a file's size to a value less than it's
previous count.
neither method is common, it seems that programmers feel that a file
will keep growing or be deleted (is that just me?).
both methods use DOS and are version dependent.
both need a routine to move the valid records to a space low in file
area. after the compaction is completed call dos (warning!!! i haven't
done this from pdox)
method 1) function 28H, ds:dx point to fcb with the file pointer at the
end of the last record, cx = 00H, call int 21 errors in al

method 2) int 21 function 40H, cx = 00h errors in ax

in the real world this process is time consuming BUT necessary.
why did the makers not realise this and give us a compact routine that
we can call easily?

eg: if shift the old stuff to archive
      and delete old stuff then then
         compact file
    else
      {*filter*}y hell, what went wrong?
      fix it and try again
    endif

if pdox wants to move into the big world it should be able to treat
archive and current files as a continuum, seamless to the programmer and
invisible across media. ie archive stored anywhere based on programmer's
whim and recoverable when needed by user without further intervention.
key fields also need to be continuous-after all there is no connection
between key fields and the data --- is there?
mick



Wed, 18 Jun 1902 08:00:00 GMT  
 TechTips: Why does the file keep on growing?

Quote:

>there are two ways to set a file's size to a value less than it's
>previous count.
>neither method is common, it seems that programmers feel that a file
>will keep growing or be deleted (is that just me?).
>both methods use DOS and are version dependent.
>both need a routine to move the valid records to a space low in file
>area. after the compaction is completed call dos (warning!!! i haven't
>done this from pdox)
>method 1) function 28H, ds:dx point to fcb with the file pointer at the
>end of the last record, cx = 00H, call int 21 errors in al
>method 2) int 21 function 40H, cx = 00h errors in ax
>in the real world this process is time consuming BUT necessary.
>why did the makers not realise this and give us a compact routine that
>we can call easily?
>eg: if shift the old stuff to archive
>      and delete old stuff then then
>         compact file
>    else
>      {*filter*}y hell, what went wrong?
>      fix it and try again
>    endif
>if pdox wants to move into the big world it should be able to treat
>archive and current files as a continuum, seamless to the programmer and
>invisible across media. ie archive stored anywhere based on programmer's
>whim and recoverable when needed by user without further intervention.
>key fields also need to be continuous-after all there is no connection
>between key fields and the data --- is there?
>mick

Much simpler, I think : encapsulate the C library's "truncate" call in a
DLL and call it from OPAL. Ain't it simpler ?




Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Memory-allocation just keeps growing out of control!

2. TechTips: 3 key steps to keeping your database alive and well

3. Why Does Pascal keep you sane.

4. why do my postings keep disapearing?

5. URGENT - Lock file grown too large - error using Paradox files

6. "lock file has grown too large"

7. Error : file has grown too large

8. Lock file has grown too large

9. The lock file has grown too large?

10. Error Blob file growing to 500MB!

11. "Lock file grown too large"

12. Lock file has grown too large - please help

 

 
Powered by phpBB® Forum Software