--- In firebird-support@yahoogroups.com, Kjell Rilbe
<kjell.rilbe@a...> wrote:

>
> Hi,
>
> What is generally most efficient - to delete all records in a table

or

> to drop it and recreate it?

I assume you mean which one is quicker, uses fewer resources and
leaves less garbage.

Dropping the table means that Firebird doesn't need to go through
each record, flag it as deleted, then do the garbage collection at
some time in the future when no other transaction is interested in
the old data.

Deleting all the records has the advantage of not having to worry
about 'object in use' errors, but deleted records still appear inside
indices until they are garbage collected, so if you have any
transactions that run for a long time, you may find these indices
have a lot of data. If you do choose to delete and repopulate, after
you commit the delete, run a select count(*) on the table to force
the garbage collection to happen.

>
> 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?

In this case I would say yes, providing you wont get problems with
objects in use by other parts of your application, this would be
quickest.

>
> Should I do a sweep after emptying the table? Backup + restore?
>

Not if you drop the table, if you delete then it wont hurt.

Adam