Deleting records before re-import (slow!)

[Mark Welch]

During the current development stage of my project, I need to delete all records from certain tables before re-importing data again.

What confuses me is the incredibly slow speed to "delete all records." It seems to be deleting one record at a time, taking more than a second per 100 records deleted.

I'm still waiting for 550,000 records to be deleted, before I can start importing again (this was already a "reduced data set" which excluded files containing another 1.5 to 2 million records of source data; for the next few cycles I'll use an even smaller set of source files). At this pace, it will take more than 6,000 seconds (100 minutes) just to delete all these records. Then, of course, it will take several hours to re-import source data.

Am I missing something here? Surely there must be a quicker way to empty out a table? (And no, I don't want to save a clone of the file, since that would sacrifice the data from my other tables.)

Edited January 26, 2009 by Guest

[IdealData]

Do you have any fields on the screen when deleting?

Any fields that are indexed may require their index to be reset after each record is deleted.

Watch out for calculation fields too, especially summaries.

[Mark Welch]

I'm not sure how "having fields on the screen" would affect this; the screen isn't updated during the deletion process.

I assume that you are right that some (perhaps most) of the delay may come from updating the index. What I don't understand is, if I'm deleting all the records from a table (with no data from the table related to any other tables), why can't the entire table contents AND all index entries for the table be deleted together, much more quickly?

Edited January 26, 2009 by Guest

[mr_vodka]

The screen still has to display any summary fields on there even for a split sec.

[Mark Welch]

Thanks. My first reaction was, how could I possibly delete all records without having the records in the current view. The obvious answer was to use a short script (go to layout, show all, delete all). Sure enough, it was much faster.