Best way to add and delete data

I have a log-database where data is added and deleted
based on a timer.

I can specify the size of the log, so when the log
is "full" I delete the oldest record when a new is added.

The adding and deleting makes the database grow untill it
is compacted.

Are there any "good practices" for this kind of procedure?

I thought about not actually adding and deleting - but
just overwrite the oldest record with the new information.
Would that save me space - and how would it affect the
performance?

I know I could just try - but any comments are appreciated.

On Tue, 4 Jan 2005 12:44:57 -0800, "Karen"
wrote:

I have a log-database where data is added and deleted
based on a timer.

I can specify the size of the log, so when the log
is "full" I delete the oldest record when a new is added.

The adding and deleting makes the database grow untill it
is compacted.

Are there any "good practices" for this kind of procedure?

I thought about not actually adding and deleting - but
just overwrite the oldest record with the new information.
Would that save me space - and how would it affect the
performance?

I know I could just try - but any comments are appreciated.

Note that this kind of operation is probably NOT appropriate for
Access! Access does NOT free up space when records are deleted or
overwritten; the database continues to grow, with the deleted or
changed records' space being marked "dead" and left unused, and if the
fields are indexed, changes to the indexes will take up space as well.
The space is only recovered when you Compact the database, as you have
seen. Changing from deleting to updating might slow the bloat a bit,
but not much.

You may want to use an old-fashioned flat-file database for this
purpose, or even write C# or VB code to manage the file size yourself.

John W. Vinson[MVP]

can I gradually "free" the space in some way as I go along?

-----Original Message-----
On Tue, 4 Jan 2005 12:44:57 -0800, "Karen"
wrote:

I have a log-database where data is added and deleted
based on a timer.

I can specify the size of the log, so when the log
is "full" I delete the oldest record when a new is added.

The adding and deleting makes the database grow untill
it
is compacted.

Are there any "good practices" for this kind of
procedure?

I thought about not actually adding and deleting - but
just overwrite the oldest record with the new
information.
Would that save me space - and how would it affect the
performance?

I know I could just try - but any comments are
appreciated.

Note that this kind of operation is probably NOT
appropriate for
Access! Access does NOT free up space when records are
deleted or
overwritten; the database continues to grow, with the
deleted or
changed records' space being marked "dead" and left
unused, and if the
fields are indexed, changes to the indexes will take up
space as well.
The space is only recovered when you Compact the
database, as you have
seen. Changing from deleting to updating might slow the
bloat a bit,
but not much.

You may want to use an old-fashioned flat-file database
for this
purpose, or even write C# or VB code to manage the file
size yourself.

John W. Vinson[MVP]
.

On Wed, 5 Jan 2005 01:51:00 -0800, "Karen"
wrote:

can I gradually "free" the space in some way as I go along?

No, only by compacting the entire database.

John W. Vinson[MVP]