Multiple Database Connections
To improve performance when you process large quantities of data, increase the number of database connections to allow multiple threads to process rows in parallel.
Enabling Multiple Database Connections
To enable multiple database connections for an archive, extract, or delete process, set Maximum Database Connections in Product Options to a value greater than one. The size of the buffer increases according to the number of database connections that you allow.
For best performance, experiment with a small number of database connections and increase the number gradually. Performance can degrade if you allow too many database connections for the number of processors that are available. Performance might also degrade if you increase the number of database connections to process small amounts of data.
Multiple threads are activated when there are more than 10,000 keys for the tables. Only tables with unique indexes are processed in parallel.
- The tables in each access definition are processed one at a time.
- Parallelism is used only for tables other than start tables.
- Optim™ uses intra-parallelism, which operates on a single table when extracting data. When Optim deletes data, Optim uses a single connection per table but can process multiple tables at a time for each relational level.
- Parallelism works best when the child tables contain a substantial number of foreign keys.
- Optim opens a separate database connection to split the keys in proportion to the number of connections.
Archive and Extract Processing
Click Database Connections on the General tab of the Archive Request Editor or Extract Request Editor to increase the maximum number of connections and select an even number, up to the site maximum.
In an archive or extract process, the start table is processed in parallel only if a point-and-shoot list is specified. Using multiple threads, a number of rows are extracted concurrently from a single table. Data is not extracted simultaneously from multiple tables, however.
Delete Processing
Click Database Connections on the General tab of the Delete Request Editor, or the Delete tab of the Archive Request Editor, to increase the maximum number of connections. Select an even number up to the site maximum.
Multiple tables can be processed in parallel, but only one connection is opened per child table. In a delete process, requests with 1000 or more rows are processed in parallel unless there are relational integrity (RI) constraints. Multiple threads (one per table) delete rows concurrently from multiple tables in the database. If Lock Tables is selected, tables are not processed in parallel.