1. Introduction : OpenAPI Concepts and Processes : Batch Statement Execution : How You Can Maximize Performance
 
Share this page                  
How You Can Maximize Performance
You can improve performance generally by following these guidelines:
If you have large batches of inserts, we recommend that you use prepared statements. Using prepared insert statements with large batches (of more than 100) can significantly improve performance.
If you are using prepared statements for batch, we recommend that you make your batches as large as possible. Larger batch sizes can make a significant difference with insert performance--even 2 or 3 times faster. In fact, when using prepared insert statements, the larger the batch, the better the performance. Batch sizes up to 100,000 have been noted to significantly improve performance.
If you must use small batches (of less than 100), then you should avoid using prepared statements. The DBMS optimization works well only for large batches; for small batches, you can achieve better performance by batching non-prepared inserts.
Faster inserts also can be achieved if the following conditions are met:
Inserts must be into a base table (not a view or index).
The table must not have any rules or integrities defined on it.
The table must not be a gateway table (for example, an IMA table, security audit log file, or an Enterprise Access table).
The inserts must be batched.
The batched statements must be an execution of a prepared dynamic insert where the dynamic parameters exactly match the values being inserted.
All but the first statement in the batch should be sent without query text, to achieve an additional performance boost. The prepared statement should be executed in batch as a single statement with multiple parameter sets (see page How Batch Statements with Multiple Parameter Sets Are Repeated).