Kbase P27293: How to disable indexes and delete records
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
02/01/2009 |
|
Status: Verified
GOAL:
When deleting a bunch of records from a table what is the best practice?
GOAL:
When deleting all the records from a table what is the best practice?
FACT(s) (Environment):
All Supported Operating Systems
Progress/OpenEdge Product Family
FIX:
When deleting a bunch of records from a table the best approach is:
1. Take a backup of the database.
2. If After Imaging is enabled disable AI.
When only deleting records there is no need to maintain the AI files, this will again speed things up.
3. Start database in multi-user mode using startup parameters of -i -B -bibuffs
-i is no integrity which means a bi file will not be maintained which will speed things up but crash recovery will not be possible is the database comes down for some reason.
4. Start an APW and BIW.
proapw db-name
probiw db-name
5. Disable Triggers
If DELETE Triggers are enabled, this can slow things down as each deletion of a record will activate a trigger to be processed, but when one is sure of the records to be deleted, the process would be speed up if the triggers are disabled when deleting large amounts of data.
DISABLE TRIGGERS FOR LOAD OF <table-name>
6. Delete records not wanted.
NOTE: If more than 50% of the table is to be deleted dumping the records to be retained and than reloading the table with the desired data may be faster.
7. Rebuild indices for the table using idxbuild or idxfix.
When deleting all the records from a table in Version 9.1B or newer, another method could be used, however, the table must reside is an Area other than the Schema Area and should only reside with other tables that will have all records deleted.
PROUTIL with the TRUNCATE AREA qualifier works by resetting the hi-water mark in the storage area back to the beginning of the storage area. This hi-water mark reset frees all of the space in the storage area for re-use. Any tables and indexes in the storage areas are initialized to the state they were in before they contained any rows or index entries. Before resetting the hi-water mark, the before image (.bi) file is truncated.
Please see solution 20256 How the enhanced proutil truncate area utility functions? if using this process.