Use the following strategies to configure Archive and your database so that Archive and Delete Processing are as efficient as possible.
The configuration strategy you choose depends on your needs and the constraints of your database processing. These strategies are not intended to solve specific performance problems but are simply suggestions to aid you in developing an archive strategy for your database, and can be tailored or even combined to fit your particular needs.
To gauge processing speed under different Archive, Extract, and Delete processing strategies, you can use the Statistical Report feature. Once you decide on a strategy, you can fine-tune Archive and your database to achieve the best results. The following strategies and tips may help you optimize your Archive, Extract, and Delete Processing.
For more information on the Statistical Report feature, see Statistical Reports.
The most basic archiving strategy uses a single Archive Request to process as many rows as possible in the shortest period of time. Speed processing is typically performed during off-peak or maintenance hours to reduce (or eliminate) competition from normal application processing. You can further speed Archive and Delete processing by increasing the number of database connections. In cases of no competition for the database, you can remove any unnecessary constraints from the database and from Archive. Another way to enhance efficiency is to design the partitioning of your database tables so that you can process the rows more easily. (For more information, see Partitioned Tables.)
To improve delete performance, you might remove any unnecessary indexes from database tables and, within Archive, disable row comparison and increase the commit frequency. You can also increase the number of database connections for Delete processing, which requires that you disable table locking. If you disable table locking, you must ensure that no modification to rows or tables occurs during processing.
A more intricate strategy for processing rows during off-peak hours is to process a single group of rows using multiple requests. Since Archive and Delete processing is light on CPU usage, creating multiple requests to process data at one time may significantly increase processing throughput. This strategy may be most beneficial for databases that cannot be modified for Archive processing (for example, by partitioning the database and removing unnecessary indexes).
The recommendations for this strategy are similar to those for the Speed Processing strategy. Within Archive, you can increase the number of database connections for Archive and Delete Processing. Additionally, for the Delete Process, you can disable row comparison and table locking and maximize the commit frequency.
The Speed Processing and Parallel Processing strategies are designed to process rows during off-peak hours. However, if you must process a significant number of rows, your off-peak window may be inadequate. Additionally, your database tables may be continuously updated and a significant shut down of database access difficult to accommodate.
In this case, you can adopt a strategy in which a small number of rows are processed regularly during normal operating hours. The archiving and deleting of such a small number of rows should not affect normal application processing. However, to ensure database integrity during the Delete Process you must ensure that table locking and compare row contents are enabled.
An additional way to increase the speed of Archive and Delete processing is to divide your database tables into partitions. Depending on the type of data in the tables, rows are sorted by frequency of access and age into the partitions. Since there is less I/O traffic to an infrequently used partition, the speed of processing rows is increased. Additionally, you could more easily disable unnecessary database indexes for a partition that is used infrequently.