The fastest way to remove all records from a database table is to use the following code example, where a variable sets the number of records deleted in the current transaction scope. This avoids bi growth (for example) by deleting records in batches of (say) 10,000 at a time.
DEFINE VARIABLE iRecord AS INTEGER NO-UNDO.
DEFINE VARIABLE iBatch AS INTEGER NO-UNDO INITIAL 10000.
OUTER:
DO WHILE TRUE TRANSACTION:
FOR EACH <YourTableNameGoesHere> EXCLUSIVE-LOCK:
iRecord = iRecord + 1.
DELETE <YourTableNameGoesHere>.
/* Commit the current transaction(iteration) and proceed with the next.
This will restart the FOR EACH block with the new first record. */
IF (iRecord MODULO iBatch) EQ 0 THEN NEXT OUTER.
END.
LEAVE.
END.
The performance of the above code can be further improved
by tuning the database for this specific task.
Do not allow other users to access the database during this exercise.
- Backup the database, due to using the no integrity (-i) parameter in the next Step.
- Start the database in multi-user mode with the no-integrity flag:
$ proserve YourDatabaseNameGoesHere -B 50000 -bibufs 60 -spin 50000 -i
The more shared memory that can be allocated to the Buffer Pool (the larger the -B value) the better, up to the amount of available physical memory or OS limits.
- Start pagewriters APW and BIW
$ proapw YourDatabaseNameGoesHere
$ probiw YourDatabaseNameGoesHere
- Start your client session and run the above code.
$ mbpro YourDatabaseNameGoesHere
For mass deletions of records from tables that have UNIQUE INDEX definitions, it is advised to follow the delete with an online IDXCOMPACT. For further information refer to Article
What does progress do when deleting records from the database? Alternative Methods:If all tables in an Area or the table is in it's own Area, Progress 9.1B introduced an offline truncate area. No matter what the size of the table, the data will no longer be available. This method takes as long as it takes to lower the high water mark (seconds). For further information refer to Article
What does the truncate area utility do?
Starting with OpenEdge 11.0, if the table is in a
Type II Storage Area, the TABLE-SCAN phrase can be used for better performance. Refer to Article
What is the benefit of using the TABLE-SCAN option? Starting with OpenEdge 12.4, if the table is in a
Type II Storage Area, the proutil tablemove truncate command like:
proutil sports2020 -C tablemove OrderLine "Data Area" "Index Area" "LOB Area" truncate (the existing database areas used by the existing table can be used, the empty table therefore does not need to be moved to new database areas)
For further alternatives, Refer to Article
Empty a Database Table without truncate area
