JDBC Batching using Oracle Driver

There's lots of reasons why using java when developing oracle based applications - especially after Oracle bought sun company. One of the main imporvements in compare to the .NET ODP is that JDBC gives us is the ability to use statement batching at a very simple development cost. No need to be familiar with Array binding or bulk operations.

Let's see how its done...

Oracle Side:

CREATE TABLE TEST (ID NUMBER, STR_VALUE VARCHAR2(10));

Java Side:

import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;

...
Connection connection;
OracleDataSource ods = new OracleDataSource();

ods.setDriverType("thin");
ods.setServerName("SERVER");
ods.setDatabaseName("SID");
ods.setPortNumber(1521);
ods.setUser("USER"); ods.setPassword("PASSWORD");
connection=ods.getConnection();

// Set the currect batch size - default value is 1
((OracleConnection)connection).setDefaultExecuteBatch(100);

PreparedStatement ps = connection.prepareStatement ("INSERT INTO test VALUES (?,?)");

ps.setInt (1,1);
ps.setString (2,"Oracle");
ps.executeUpdate(); // Nothing actually happens here

ps.setInt (1,2);
ps.setString (2,"SQL Server");
ps.executeUpdate(); // Nothing actually happens here either - until we get to the batch size

int total_rows = ((OraclePreparedStatement)ps).sendBatch (); // Or we can just tell JDBC we're done using this statement :)

Checking the shared pool will show us just one execution.

I Like Java - it keeps the things simple!

Using NHibernate Batching with Oracle 11g

I was supposed to find a solution for NHibernate's low performance with massive inserts.

Searching the web didn't help much and although java's version of hibernate clearly used batching efficiently, I Had to struggle the .NET Version to use the batching mode.

Basicly, an average insertion of a record took 1 mili-second (0.972113 to be precise) - Using the batching mode i've made it last just 0.06 mili-second that's a 1600% improvement!

The most crucial difference between Hibernate and NHibernate is the fact that Hibernate is able to re-order the commands once we flushed the data and set the configuration right - While NHibernate has this facility missing, not to worry - a simple solution is avilable - reorganise the savings.

So first, you should set "ado.batch_size" to the desired value, then take your list of entites and save all the low-level properties (nhibernate bags for example), then save the entity itself.

I would recommend you to turn on the NHibernate logging, and check that the similar SQL commands are tied to each other.

The final check should be at the oracle side, look for your insert at the shared pool (v$sql) and check the execution count each run - that's should do the work!