Subject Re: [firebird-support] Delete all recs or drop & recreate table?
Author Aage Johansen
Kjell Rilbe wrote:
> Hi,
> What is generally most efficient - to delete all records in a table or
> to drop it and recreate it?

If you don't have any (or just a few) dependencies then a "drop" will be
easy and fast. I believe the table's pages will just be made available for
any purpose in the db.
If maintaining (recreating) dependencies is just too complicated then
"delete" might be better. Backup (without garbage collection (GC)) and
restore before anyone selects from the table. The time for GC or sweep
will depend on indexes (and their selectivity).

Aage J.

> The table in question has 50-60 columns varying in size from char(1) and
> int to varchar(80). It holds about 1 million records. About 25 of the
> columns are indexed. Firebird 1.5.
> After being emptied, the table will be filled again with similar (but
> updated) data from a couple of external sources.
> I want to minimize the time it takes to empty the table. I generally
> drop indices, delete all records, insert new data, create indices.
> Would it be better to drop table, recreate table, insert new data,
> create indices?
> Should I do a sweep after emptying the table? Backup + restore?
> This batch is executed 1-2 times a month.
> Thank you,
> Kjell