Re: Array Inserts from Java JDBC

From: Job Miller <jobmiller_at_yahoo.com>
Date: Fri, 1 Feb 2013 10:00:19 -0800 (PST)
Message-ID: <1359741619.55965.YahooMailNeo_at_web126102.mail.ne1.yahoo.com>

The docs cover this pretty well:
http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraperf.htm#i1056232

here is a blog as well:

http://betteratoracle.com/posts/25-array-batch-inserts-with-jdbc

---

Update Batching
You can reduce the number of round-trips to the database, thereby improving application performance, by grouping multiple UPDATE, DELETE, or INSERTstatements into a single batch and having the whole batch sent to the database and processed in one trip. This is referred to as update batching. Note:The JDBC 2.0 specification refers to update batching as batch updates. This is especially useful with prepared statements, when you are repeating the same statement with different bind variables. Oracle JDBC supports two distinct models for update batching:

• The standard model, implementing the JDBC 2.0 specification, which is referred to as standard update batching
• The Oracle-specific model, independent of the JDBC 2.0 specification, which is referred to as Oracle update batching Note:It is important to be aware that you cannot mix these models. In any single application, you can use one model or the other, but not both. Oracle JDBC driver will throw exceptions when you mix these. This section covers the following topics:
• Overview of Update Batching Models
• Oracle Update Batching
• Standard Update Batching
• Premature Batch Flush

Oracle Update Batching
The Oracle update batching feature associates a batch value with each prepared statement object. With Oracle update batching, instead of the JDBC driver running a prepared statement each time the executeUpdate method is called, the driver adds the statement to a batch of accumulated processing requests. The driver will pass all the operations to the database for processing once the batch value is reached. For example, if the batch value is 10, then each batch of 10 operations will be sent to the database and processed in one trip. A method in the OracleConnection class enables you to set a default batch value for the Oracle connection as a whole, and this batch value applies to any Oracle prepared statement in the connection. For any particular Oracle prepared statement, a method in the OraclePreparedStatement class enables you to set a statement batch value that overrides the connection batch value. You can also override both batch values by choosing to manually process the pending batch.

OracleDataSource ods = new OracleDataSource();

ods.setURL("jdbc:oracle:oci);
ods.setUser("scott");
ods.setPassword("tiger"); Connection conn = ods.getConnection();

conn.setAutoCommit(false); PreparedStatement ps = conn.prepareStatement("insert into dept values (?, ?, ?)"); //Change batch size for this statement to 3 ((OraclePreparedStatement)ps).setExecuteBatch (3); ps.setInt(1, 23);

ps.setString(2, "Sales"); 
ps.setString(3, "USA"); 
ps.executeUpdate(); //JDBC queues this for later execution  ps.setInt(1, 24); 
ps.setString(2, "Blue Sky"); 
ps.setString(3, "Montana"); 
ps.executeUpdate(); //JDBC queues this for later execution  ps.setInt(1, 25); 
ps.setString(2, "Applications"); 
ps.setString(3, "India"); 
ps.executeUpdate(); //The queue size equals the batch value of 3  //JDBC sends the requests to the database ps.setInt(1, 26); 
ps.setString(2, "HR"); 
ps.setString(3, "Mongolia"); 
ps.executeUpdate(); //JDBC queues this for later execution ((OraclePreparedStatement)ps).sendBatch(); // JDBC sends the queued request

conn.commit(); ps.close();
...

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 01 2013 - 19:00:19 CET