Performance issue after mass data deletion in the database

Performance issue after purging large amounts of data in the database
Database started in multi-user mode at the time of the purge

Various tables had a very large number of records deleted
4GL/ABL queries against the tables involved in bulk delete perform are much slower after the bulk delete than they did before (up to 200 times in extreme cases)
Affected queries use a UNIQUE INDEX on the table


 

No index rebuild or index compaction performed on affected tables after mass record deletion
This issue does not affect temp-tables. Temp-tables behave the same as a single-user database.

When index entries in a unique index are deleted, they are replaced by delete placeholders that reserve the entry until the transaction commits.
This is done so that another transaction running at the same time cannot add the same unique entry, which would thereby prevent the deleting transaction from rolling back.

These placeholders are no longer needed after the deleting transaction commits. They are cleaned out the next time the index block that contains them is updated and written to disk. However, if the block is not written to disk again because there are no modifications to it, the placeholders remain.

When read queries traverse the entries in an index bracket, the placeholders are skipped over. When there are a large number of placeholders within a bracket, possibly in multiple index blocks (which often occurs when one deletes many rows in a table), this can cause noticeable delays and extra index block reads.

1. Run either an offline index rebuild (PROUTIL IDXBUILD) or an index compaction (PROUTIL IDXCOMPACT) on the tables which had large amounts of data purged.

• PROUTIL IDXCOMPACT can be run online, but doing so it may skip over parts of an index to avoid contention with other processes/clients. Multiple online runs may be required to bring performance back to normal.
• PROUTIL IDXBUILD must be run offline and will only require a single run to clear out all placeholders.

Corrective actions should be considered after delete operations in order to reduce the number of index blocks in an effort to increase index utilization and therefore performance:

1. Ideally, an IDXBUILD operation on the affected table(s) would correct this immediately as index entries are rebuilt from scratch, thereby clearing out all placeholders. This utility has to be run offline.  
2. If the database cannot be taken offline, after the delete operation:

• The "proutil -C idxcompact" operation, can be run both online and offline to reduce the number of blocks in the B-Tree and possibly the number of B-Tree levels. When run online it may skip over parts of an index to avoid contention with other processes/clients. Multiple online runs may be required to bring performance back to normal. The IDXCOMPACT algorithm has been improved in OpenEdge 12.5, 12.2.7, 11.7.11 to more efficiently clean up delete holders/blocks full of delete holders in it's first run: IDXCOMPACT does not compact indexes completely the first time  
• Use "proutil -C idxmove" to another area and optionally back again to the current area the indexes are stored. This action will rebuild the indexes of the remaining records in this table.
• A new option has been added in 12.6 for the proutil idxcompact operation, "UNUSEDBLOCKS". Primarily intended for use after large record purge operations have been performed. This option causes PROUTIL IDXCOMPACT to scan only the delete chain and clean up blocks if the index is a unique index. This option also improves OLTP performance and speeds up certain PROUTIL IDXCOMPACT operations.

2. An alternative is to stop the database and run the mass record deletion in single-user mode.
In single-user mode there is no risk of conflict between transactions, therefore the delete placeholder mechanism isn't used as it isn't needed.

3. As this is working as designed an enhancement to the product can be requested through the Progress Community via an Ideas submission. Customer feedback is valuable and Idea submissions are monitored by our Product Management team. Enhancement requests are reviewed during the planning phase of each new product release and a list of the enhancements chosen for implementation can be found in the Release Notes documents that accompany each release. Once an Idea is submitted the Progress Software Community will have the opportunity to comment on and vote for the Idea.

For detailed information on how to submit an Idea, please refer to Knowledge Base article How to submit an idea for a Progress product enhancement.

Progress Articles:

 What does progress do when deleting records from the database?  
 Why deleting records in a table leads to index fragmentation ?  
 What is the fastest way to remove all records from a database table?