• 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.

• 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).