Efficient way to do batch INSERTS with JDBC

Question

In my app I need to do a lot of INSERTS. Its a Java app and I am using plain JDBC to execute the queries. The DB being Oracle. I have enabled batching though, so it saves me network latencies to execute queries. But the queries execute serially as separate INSERTs:

insert into some_table (col1, col2) values (val1, val2)
insert into some_table (col1, col2) values (val3, val4)
insert into some_table (col1, col2) values (val5, val6)

I was wondering if the following form of INSERT might be more efficient:

insert into some_table (col1, col2) values (val1, val2), (val3, val4), (val5, val6)

i.e. collapsing multiple INSERTs into one.

Any other tips for making batch INSERTs faster?

Answer 1

You'll have to benchmark, obviously, but over JDBC issuing multiple inserts will be much faster if you use a PreparedStatement rather than a Statement.

Answer 2

The Statement gives you the following option:

Statement stmt = con.createStatement();

stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')");
stmt.addBatch("INSERT INTO emp_dept VALUES (1000, 260)");

// submit a batch of update commands for execution
int[] updateCounts = stmt.executeBatch();

Answer 3

This is a mix of the two previous answers:

  PreparedStatement ps = c.prepareStatement("INSERT INTO employees VALUES (?, ?)");
  ps.setString(1, "John"); ps.setString(2,"Doe"); ps.addBatch(); ps.clearParameters();
  ps.setString(1, "Dave"); ps.setString(2,"Smith"); ps.addBatch(); ps.clearParameters();
  int[] results = ps.executeBatch();

Answer 4

How about using the INSERT ALL statement ?

INSERT ALL

INTO table_name VALUES ()

INTO table_name VALUES ()

...

SELECT Statement;

I remember that the last select statement is mandatory in order to make this request succeed. Don't remember why though. You might consider using PreparedStatement instead as well. lots of advantages !

Farid

Answer 5

Using PreparedStatements will be MUCH slower than Statements if you have low iterations. To gain a performance benefit from using a PrepareStatement over a statement, you need to be using it in a loop where iterations are at least 50 or higher.