Batch Statement Execution
To take advantage of the batch query execution capability of the Ingres DBMS, you can use the addBatch and executeBatch methods supported by the JDBC Driver.
Batched statements that use repeated dynamic INSERT statements (for example, through Java-based Extract Transfer and Load tools) are specially optimized to improve performance.
If you want to force individual statement execution, use the ingres.jdbc.batch.enabled system property (see
System Properties) to disable batch query execution. If the DBMS does not support batch processing, the JDBC Driver detects it and automatically executes the statements individually.
We recommend not using autocommit with batch execution. If batch execution is used with autocommit, and you cancel the batch execution, it is impossible to tell which statements were committed.
You can improve performance of batch statement processing 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.
Batch Statement Execution Example
Here is a Java code segment that shows batched inserts using prepared statements:
PreparedStatement prep = conn.prepareStatement( "insert into emp( id, name ) values( ?, ? )" );
prep.setInt( 1, 1001 );
prep.setString( 2, "Adam Anderson" );
prep.addBatch();
...
prep.setInt( 1, 1099 );
prep.setInt( 2, "Barry Bradley" );
prep.addBatch();
int results[] = prep.executeBatch()
Last modified date: 08/28/2024