[ start | index | login ]

Tschou zäme (*)


Saturday, 16. March 2013

JDBC Batch Insert Performance

Not the first time I asked myself what the speedup of JDBC batch inserts is and I finally hacked a dirty test:

Insert Technologyexecution [ms]number of insertsinserts per [s]
PreparedStatement: Single inserts58483100'0001709
PreparedStatement: Batch insert1896100'00052742
PreparedStatement: >>Oracle batching3080100'00032467

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?

As you know the execution of a batch with the JDBC-API returns status information for each element of the batch:
int[] java.sql.Statement.executeBatch()
Each int can have different values and Oracle will always return -2 for every single one (-2 is "SUCCESS_NO_INFO": update worked, but no information is available how many rows were affected by this command).

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 ;-)
no comments | post comment

Tuesday, 10. January 2012

Upgrade unison on QNAP NAS TS-

In order to remember how this was done (again)…
  • 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
no comments | post comment

Friday, 28. May 2010

Netcetera CodeCamp 10.05

To remember: PermaLink
no comments | post comment

Friday, 05. March 2010

Felipe, in memoriam

Today, Felipe went from us. Deeply moved I am hardly capable to realize what mother nature asks us to accept.

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. PermaLink

no comments | post comment

Wednesday, 29. October 2008

Certa Fides online

As a next domain I am happy to announce >>http://www.certa-fides.ch and >>http://www.certafides.ch to go online.


no comments | post comment

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 :-)

search www.stefanrufer.ch



Blog Calendar

< June 2012 >

Weblog summary 2007, 2006, 2005, 2004

Content managed by SnipSnap


snipsnap.org | Copyright 2000-2002 Matthias L. Jugel and Stephan J. Schmidt