2 Replies Latest reply on Apr 29, 2002 4:16 PM by Dain Sundstrom

    jdbc batch updates?

    Simon Sadedin Newbie


      searching around, couldn't find anything on this. With straight JDBC it is often possible to save a fair number of round trips to the database by using a JDBC "batch update" - ie. use stmt.addBatch() to batch several updates/inserts together then and stmt.executeBatch() to fire them off.

      Looking at the performance on the system I work with, it seems like some of our larger transactions doing a fair number of small updates could benefit from such an optimization.

      Anyone know a way to acheive this using jboss CMP?

      Would it be a reasonable thing for an app server to undertake in the CMP layer?



        • 1. Re: jdbc batch updates?
          Jürgen Zeller Newbie


          the "batch update" is good for performance, but
          very difficult (read: impossible) to implement.

          Why? You have to track the open Statement object
          (to call .addBatch()) and have to execute() it
          before you do the next SELECT in the same transaction.
          At that point, any failure in the batched
          Statement can't be handed back to "the right place",
          so most usefull error handling (e.g. checking for a
          DuplicateKeyException) won't work as expected.

          => without a semantic change in a future
          EJB spec, i think that batch updates are not
          possible within an EJB Container, at least not
          as long it conforms to the EJB spec.



          • 2. Re: jdbc batch updates?
            Dain Sundstrom Master

            There are places where I will use batch updates in the future. For example, relation table inserts and deletes can easily be batched. It may be possible to general creates or deletes, but updates would be difficult.