Announcement Announcement Module
No announcement yet.
Batch Insert with NamedParameterJdbcTemplate? Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Batch Insert with NamedParameterJdbcTemplate?

    Is it possible to perform a batch insert/update using the NamedParameterJdbcTemplate to provide the value binding?

    Not to knock Spring (I'm a big fan), but shouldn't all of the Spring JDBC API's support batching? On an Oracle system [in our environment] we benchmarked batched inserts at 66,000 rows/sec vs. ~2500 rows/sec non-batched. It is nearly impossible to write anything that is "Enterprise-class" without batching. By "Enterprise-class" I mean tens of millions of transactions per day. Given that raw JDBC is capable of it (easily), Spring's wrappers wouldn't add much overhead.


  • #2
    Is it possible to see the kind of thing you are trying to do?


    • #3
      I can give you a simple example of what we would like to do. We are processing a large XML file with thousands of rows like this one:

      <zds:acl name="inside_access" action="permit" proto="udp" src-ip="" src-mask=""/>

      I would like to perform an INSERT like the following (in NamedParameterJdbcTemplate lingo):

      INSERT INTO acl (action, proto, dst_ip, dst_mask, src_ip, src_mask) VALUES (:action, : proto, :dst-ip, :dst-mask, :src-ip, :src-mask)

      Using NamedParameterJdbcTemplate it is trivial to automatically map the XML to the query. I can ensure that the XML attributes always match the named parameters; with the exception that some of the attributes may not be present in the XML (resulting in NULL for those columns).

      In the absence of NamedParameterJdbcTemplate, we will need to implement our own mapping of names to positional parameters. However, doing so is non-trivial as the SQL string must be processed and account for "false parameters" that may actually be quoted data containing a colon followed by a string. Which while not rocket science is rather a pain and a seemingly needless duplication of functionality. Maybe Spring can be made to do it and am hoping it can; that's why I asked my question here.

      Given that we need to INSERT (tens of) thousands of these rows for every document that we process, and given that we have thousands of such documents to process (daily), it would be insanity not to use batching. Our benchmarks emulating this document processing indicate that we can process and BATCH insert a document with 20,000 such entries in about 300ms. If we do not utilize a batch, and naively pepper the DB with one INSERT per row, time to process the same document exceeds 10 seconds.

      If we didn't need near-realtime availability we would simply bulk load the several million rows all at once; once daily. However, we are in fact near-realtime and therefore don't have that option.

      Sorry for the long post, and thanks for any help you can provide,