Batch processing during database write operations
Batch processing improves database performance by reducing the number of statements that are run against the database. During batch processing, write operations to the same database table are batched into statements according to the batch size. Batch size is the maximum number of write operations to include in a batch. When a transaction ends, all batches that did not exceed the batch size are also run.
For example, assume a batch size of 100. If a transaction has 160 save operations to table A and 140 save operations to table B, there will be 4 batches:
- A batch with 100 operations to table A
- A batch with 60 operations to table A
- A batch with 100 operations to table B
- A batch with 40 operations to table B
By default, batch database processing is enabled. If a table in the transaction has a foreign key dependency on another table that is used in the same transaction, batch processing is disabled for the duration of the transaction, and the rows are written in the order that they were received. Disabling batch processing avoids the possibility of getting a constraint violation from the database and thus failing to commit the transaction. A constraint violation might occur when operations are reordered. For example, if record A is created in table A and record B is created in table B, and table B has a foreign key dependency on record A in table A, a constraint violation could occur if table B is saved before table A exists.
If the Pega® Platform fails to retrieve information about the relationships between tables, for example, when the database user that the Pega Platform uses to access the database does not have privileges to query system tables, a validation error might occur. In this case, you can disable batch processing for the database by using the
database/databases/<dbname>/batchSize setting in the prconfig.xml file, where
dbname is the name of the Data-Admin-DB-Name of the database. This setting can have the following values:
- 0 – Enables batch processing. All write operations to the same table are batched into a single statement.
- -1 – Disables batch processing
- Positive number – Enables batch processing and specifies the maximum number of operations to batch into a single statement.