By default the Write to DB stage commits after each row is inserted into the table. However, to improve performance enable the Batch commit option. When this option is enabled, a commit will be done after the specified number of records. Depending on the database this can significantly improve write performance.
When selecting a batch size, consider the following:
- Data arrival rate to Write To DB stage: If data is arriving at slower rate than the database can process then modifying batch size will not improve overall dataflow performance. For example, dataflows with address validation or geocoding may not benefit from an increased batch size.
- Network traffic: For slow networks, increasing batch size to a medium batch size (1,000 to 10,000) will result in better performance.
- Database load and/or processing speed: For databases with high processing power, increasing batch size will improve performance.
- Multiple runtime instances: If you use multiple runtime instances of the Write to DB stage, a large batch size will consume a lot of memory, so use a small or medium batch size (100 to 10,000).
- Database roll backs: Whenever a statement fails, the complete batch is rolled back. The larger the batch size, the longer it will take to perform the to rollback.