title: Oracle and JDBC performance: INSERT ALL vs preparedStatement.addBatch
body: <p>I have a java app with an Oracle database backend that I need to insert multiple rows into. I've seen the discussion about <a href="https://stackoverflow.com/questions/39576/best-way-to-do-multi-row-insert-in-oracle#39602">inserting multiple rows into Oracle</a>, but I'm also interested in how the performance is affected when JDBC in thrown in the mix.</p>
  
  <p>I see a few possibilities:</p>
  
  <p>Option 1:
  Use a singe-row insert PreparedStatement and execute it multiple times:</p>
  
  <pre><code>String insert = "Insert into foo(bar, baz) values (?, ?)";
  PreparedStatement stmt = conn.prepareStatement(insert);
  for(MyObject obj : someList) {
      stmt.setString(1, obj.getBar());
      stmt.setString(2, obj.getBaz());
      stmt.execute();
  }
  </code></pre>
  
  <p>Option 2:
  Build an Oracle INSERT ALL statement:</p>
  
  <pre><code>String insert = "INSERT ALL " +
      "INTO foo(bar, baz), (?, ?) " +
      "INTO foo(bar, baz), (?, ?) " +
      "SELECT * FROM DUAL";
  PreparedStatement stmt = conn.prepareStatement(insert);
  int i=1;
  for(MyObject obj : someList) {
      stmt.setString(i++, obj.getBar());
      stmt.setString(i++, obj.getBaz());
  }
  stmt.execute();
  </code></pre>
  
  <p>Option 3:
  Use the addBatch functionality of PreparedStatement:</p>
  
  <pre><code>String insert = "Insert into foo(bar, baz) values (?, ?)";
  PreparedStatement stmt = conn.prepareStatement(insert);
  for(MyObject obj : someList) {
      stmt.setString(1, obj.getBar());
      stmt.setString(2, obj.getBaz());
      stmt.addBatch();
  }
  stmt.execute();
  </code></pre>
  
  <p>I guess another possibility would be to create a CSV file and use the SQL Loader, but I'm not sure that would really be faster if you add in the overhead of creating the CSV file...</p>
  
  <p>So which option would perform the fastest?</p>