Hi,

SK> The problem is that we must remove old data from the trend table. We
SK> are supposed to store data for a week which means about 4-6 million
SK> rows. I haven't been able to write such question to database (delete
SK> everything that is older than a week) that dosn't take very long
SK> time and use a lot of CPU :(

How often do you run this DELETE? Once a week? Every day?

SK> And even if you do such thing, the size of the database doesn't
SK> shrink a single byte!!!

This is to be expected, since Interbase reuses the space that was
occupied by the deleted records. And, if you look at MS-SQL and
Oracle, you'll see the same behavior.

SK> We tried to use an table attached to an external file instead. But
SK> the database grow alot anyway. Why????

This is probably due to the multi-generational architecture of
Interbase, which permits multiple versions of a row to exists until
all transactions related to that row are committed. That is just what
I understand of the concept. You probably would want to look at the
database stats and verify the transaction numbers and make sure that
the oldest transaction is not too much of a big difference with the
most active transaction.

SK> Is there any one that have done something similar and solved this
SK> problem? I guess that all logger applications have this problem.

SK> Why is the database growing even if we only do UPDATE on one TABLE
SK> and stores data in an external table?

See explanation above.

SK> Is it somthing I can so to eliminate the constant growth of the
SK> database.

I would suggest that you run through the process a few times so you
can see about at what iteration the database size starts to stabilize
and what the size would be. But, also keep in mind that it is a good
idea to do a backup and restore on a regular interval as a database
maintenance(maybe once every 6 months or whenever you notice is the
best time to do it)



Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.accramed.ca)