Bulk insert in Java using prepared statements batch update

by

I’ll address your questions in turn.

  • Will the executeBatch method tries to send all the data at once?

This can vary with each JDBC driver, but the few I’ve studied will iterate over each batch entry and send the arguments together with the prepared statement handle each time to the database for execution. That is, in your example above, there would 50,000 executions of the prepared statement with 50,000 pairs of arguments, but these 50,000 steps can be done in a lower-level “inner loop,” which is where the time savings come in. As a rather stretched analogy, it’s like dropping out of “user mode” down into “kernel mode” and running the entire execution loop there. You save the cost of diving in and out of that lower-level mode for each batch entry.

  • Is there a way to define the batch size

You’ve defined it implicitly here by pushing 50,000 argument sets in before executing the batch via Statement#executeBatch(). A batch size of one is just as valid.

  • Is there any better way to speed up the process of bulk insertion?

Consider opening a transaction explicitly before the batch insertion, and commit it afterward. Don’t let either the database or the JDBC driver impose a transaction boundary around each insertion step in the batch. You can control the JDBC layer with the Connection#setAutoCommit(boolean) method. Take the connection out of auto-commit mode first, then populate your batches, start a transaction, execute the batch, then commit the transaction via Connection#commit().

This advice assumes that your insertions won’t be contending with concurrent writers, and assumes that these transaction boundaries will give you sufficiently consistent values read from your source tables for use in the insertions. If that’s not the case, favor correctness over speed.

  • Is it better to use a updatable ResultSet or PreparedStatement with batch execution?

Nothing beats testing with your JDBC driver of choice, but I expect the latter—PreparedStatement and Statement#executeBatch() will win out here. The statement handle may have an associated list or array of “batch arguments,” with each entry being the argument set provided in between calls to Statement#executeBatch() and Statement#addBatch() (or Statement#clearBatch()). The list will grow with each call to addBatch(), and not be flushed until you call executeBatch(). Hence, the Statement instance is really acting as an argument buffer; you’re trading memory for convenience (using the Statement instance in lieu of your own external argument set buffer).

Again, you should consider these answers general and speculative so long as we’re not discussing a specific JDBC driver. Each driver varies in sophistication, and each will vary in which optimizations it pursues.

More Related Contents:

  1. Getting java.sql.SQLException: Operation not allowed after ResultSet closed
  2. PreparedStatement with list of parameters in a IN clause [duplicate]
  3. Using setDate in PreparedStatement
  4. Java: Insert multiple rows into MySQL with PreparedStatement
  5. How can I get the SQL of a PreparedStatement?
  6. Get query from java.sql.PreparedStatement [duplicate]
  7. How does a PreparedStatement avoid or prevent SQL injection?
  8. Using Prepared Statements to set Table Name
  9. right syntax to use near ‘?’
  10. How to use an arraylist as a prepared statement parameter [duplicate]
  11. When to close Connection, Statement, PreparedStatement and ResultSet in JDBC
  12. How does Java’s PreparedStatement work?
  13. Most efficient conversion of ResultSet to JSON?
  14. When should we use a PreparedStatement instead of a Statement?
  15. JDBC driver throws “ResultSet Closed” exception on empty ResultSet
  16. preparedStatement syntax error
  17. JDBC ResultSet get columns with table alias
  18. Get Number of Rows returned by ResultSet in Java
  19. PreparedStatement setNull(..)
  20. How to insert multiple documents at once in MongoDB through Java
  21. ResultSet.getString(1) throws java.sql.SQLException: Invalid operation at current cursor position
  22. Having a Column name as Input Parameter of a PreparedStatement
  23. PreparedStatements and performance
  24. ResultSet: Exception: set type is TYPE_FORWARD_ONLY — why?
  25. How to get the number of columns from a JDBC ResultSet?
  26. How do I get the row count in JDBC?
  27. Oracle’s RETURNING INTO usage in Java (JDBC, Prepared Statement)
  28. Convert a Result Set from SQL Array to Array of Strings
  29. JDBC Prepared Statement . setDate(….) doesn’t save the time, just the date.. How can I save the time as well?
  30. PreparedStatement and setTimestamp in oracle jdbc

Leave a Comment