jdbc batch updates?

[ssadedin]

All,

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?

Cheers,

Simon.

[zetzet]

Hi,

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.


Bye,

Jürgen

[dsundstrom]

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.