Tschou zäme (*)

Weblog-Roller

JDBC Batch Insert Performance

Insert Technology	execution [ms]	number of inserts	inserts per [s]
PreparedStatement: Single inserts	58483	100'000	1709
PreparedStatement: Batch insert	1896	100'000	52742
PreparedStatement: Oracle batching	3080	100'000	32467

There could be a lot of fine-grained optimizations (try out if commit in the middle makes it faster, different batch sizes, varying test data, creating new prepared statement for each statement...). However, the results should be enough to see the order of magnitude and give you the hint to consider batching if you insert/update more than 1000 commands a time.

The test did run on a server with CentOS Linux 2.6.9 / Intel Xeon 2.33 GHz CPUs, DB runs on a similar host and they are connected with a 100MBit link.

Why using Oracle specific batching?

int[] java.sql.Statement.executeBatch()

Now, if you want to do batch inserts (and especially updates) you may want to know precisely how many rows your command affected. It might be that there's a bogus WHERE clause and some updates in the batch failed to do what you intended.

You can do two things now:

1. rely on your unmatched ability to write error-free SQL statements
2. use Oracle batching as follows:

int numRows = 0;
for(int i = 0; i < parameters.length; i++) {
  ps.setInt(i+1, parameters[i]);
  // sum up all rows affected by executeUpdate
  numRows = ps.executeUpdate();
}
// send remaining rows to the DB whether batch is complete or not  
numRows += ((OraclePreparedStatement)ps).sendBatch ();
if (numRows != parameters.length) {
  // … your fancy error handling goes here
}

The "fancy error handling" in our case means falling back to single statement JDBC operations, say:

1. roll back the batch operation
2. execute standard PreparedStatements, one after another
3. where the update count of the executeUpdate method is not one: found your bogus SQL ;-)

Upgrade unison on QNAP NAS TS-

• first you need the QPKG plugin installed, find this in your QNAP admin interface. To check if it's there run: /opt/bin/ipkg
• then upgrade your dusty packages using: /opt/bin/ipkg upgrade
• (unverified step) make sure ocaml is installed, otherwise run: /opt/bin/ipkg install ocaml
• download the unison version of your choice, eg: cd share/HDA_DATA/custom; wget http://www.seas.upenn.edu/~bcpierce/unison//download/releases/unison-2.27.57/unison-2.27.57.tar.gz
• gunzip unison-2.27.57.tar.gz; tar -xvf unison-2.27.57.tar.gz
• Finally build unison in non-native (no native ocaml compiler or I don't know how to) and text style mode (no need for a GUI on the NAS): cd unison-2.27.57; make NATIVE=false UISTYLE=text

Netcetera CodeCamp 10.05

• http://webapp-net.com/
• http://iwebkit.net/
• http://code.google.com/p/iui/

Felipe, in memoriam

In my eyes he was one of the most prominent open source contributors at Netcetera. But that was just his technical skill - moreover he was an enthusiastic, positive man that carried the sunshine inside him.

Lacking the proper words to describe what has happened I am trying to look forward for the bright memory of Felipe to supersede the sorrow.

Certa Fides online

Last but not least

This page is maintained in english mainly but you may find some occasional german snips. Even tough this webpage is run by a Wiki software, the pages are not publicly editable.

(*) "Tschou zäme" is a greeting in Swiss German that says "Hi everyone". It is particularly from the area where my roots are: The Emmental region near Berne, Switzerland. And yes, indeed, this is where the famouse Emmental Cheese has it's origin :-)