Hey all,
I'm working on an old (jboss4) environment and trying to do some tuning while not upgrading the platform (changing what I can control).

One area is a BULK/BATCH INSERT section (>1000 inserts at once) on an MS SQL Server that has a lot of stored procedures.

Changes: I've setup the model to try to increase the BULK/BATCH INSERT (they are all insert, not updates) as follows (using 'InsertModel' for reference).

1) Change InsertModel ID generator to TABLE_GENERATOR to avoid relying on the MS SQL identity during a BULK INSERT of >1000 items
2) setup JDBC batch size (currently at 1000, not sure if that is too high or not).
3) Set flushmode to COMMIT instead of AUTO -- note, this is a JavaEE app not javaSE, so relying on the built-in transaction support.
4) modify the insert manager code to loop through 1000 at a time before doing em.flush()/em.clear() to the JDBC batch size

After making all those changes, 10,000 inserts went from 15 seconds down to 2.5 seconds...in a controlled environment where I removed the IDENTITY column of the table in question.

PROBLEM:
As I mentioned, the MS SQL server has a lot of stored procedures that also do inserts to the table beneath the 'InsertModel', and the sprocs are relying on the IDENTITY (Eventually these will all be changed, but too big to tackle right now). The above changes DO NOT WORK with the IDENTITY unless there is some way, in JavaEE, to set IDENTITY_INSERT to ON during the batch insert operations (or any operation with the model....). How can this be accomplished? (preferably with JPA-only, but if hibernate specific is there a way to still have it be managed by the transaction for rollback purposes)

Thanks for any input...refactoring old code to improve it without breaking it is always tough :-)

EDIT: Along these lines, is there a programmatic way to get the Dialect of the database connected to through JPA or Hibernate to help select the 'IDENTITY_INSERT' code only when MS SQL is detected? (currently it will always be MS SQL, but the long term is to migrate everything to be DB-agnostic but keep appropriate tuning coded behind this check).

-Darren