SoftTech wrote:

> Greetings All,
>
> Firebird 1.5.3
> Delphi 5.1
>
> My goal is to do house keeping on all of the history tables in our

database and keep the last 30 days on some tables and the last 30
activities on others. Then do a backup and restore to make the size
of the database smaller.

>
> Please keep in mind that I did the following on a backup of the

production database so that I could test my work and not affect the
production database until I'm sure this will work.

>
> One of the tables in our database (DIALER_RESULTS) stores results

from a predictive dialer. The table currently has over 5-1/2 million
records in it and we have decided to do some house keeping on this
table by keeping the last 30 activity results for each account and
deleting the rest of the records.

>
> I have created a program that selects all records from the

DIALER_RESULTS_ACCT table into a TClientDataSet (This table has just
one field ACCT_ID) and then loop through each record in this dataset
which then calls a stored procedure that selects all records for this
account in descending order and deletes any record past the first 30 records.

>
> I ran this program on a weekend with no one else around (as I ran

it on the same server used for the production database) and it
successfully did the job in several hours returning that over 3-1/2
million records were deleted.

>
> Q: Should I have done a select statement against this database to

force garbage collection?

If there are many users connected: Maybe not now.
If there are just a few users connected: Maybe yes - just those few
will be annoyed about long(er) response times.
If no users are connected: No, do a sweep instead.

> Q: Is it a good thing to delete this many records at one time from

one table?

I wouldn't worry. It would probably be a good thing if your program
issued some commits during that process. Maybe after finishing the
delete for each individual account.

> Q: Any gotchas when it comes to doing a backup and restore? The

backup normally takes a few hours to run on a 6-1/2 GB
database. Will the backup take longer now because of the mass delete?

If you immidiately restore you should run the backup without garbage
collection. Any GC will just take additional time - the restored
database will not contain the remains of the deleted records anyway.
As always, restore to a new database - don't overwrite the old one
before the new database is verified to be OK.

> Q: Any guidelines available on the best ways to do a mass delete

and what all should be done to make the size of the database smaller?

Usually there is no need to make the database smaller. The space
that is released will be reused by the database as new records are
added. Also, it should be (very slightly) quicker to find a free
page in the database rather than asking the operating system for more space.


--
Aage J.