Hi Andrus, Thanks for the quick reply. I'm going to read those docs, and give your suggestion a go. I'll let you know how I get on.
Marc On Wed, Jul 13, 2011 at 8:31 PM, Andrus Adamchik <[email protected]> wrote: > > Hi Marc, > > first a small clarification - dataContext.commitChanges() commits objects in that context. Since all your changes are done with SQLTemplates, it probably does nothing. I.e. DataContext itself is a "disconnected" object, and by default each operation is wrapped in its own transaction automatically (something you are correctly observing here). > > So to do everything in one transaction, use manual transactions per http://cayenne.apache.org/doc30/understanding-transactions.html > > Transaction.bindThreadTransaction(tx); > > try { > // run SQLTemplates > .... > // if no failures, commit > tx.commit(); > } > catch (Exception ex) { > tx.setRollbackOnly(); > } > finally { > Transaction.bindThreadTransaction(null); > > if (tx.getStatus() == Transaction.STATUS_MARKED_ROLLEDBACK) { > try { > tx.rollback(); > } > catch (Exception rollbackEx) { > } > } > } > > Cheers, > Andrus > > On Jul 13, 2011, at 8:00 PM, Marc O'Morain wrote: > > > Hi there, > > > > I have an application that needs to perform a lot (~4000) of "INSERT .. ON > > DUPLICATE ..." operations to a MySQL databases. My Java code looks like > > this: > > > > long id = ....; > > long time = ....; > > DataContext dataContext = .....; > > > > dataContext.performNonSelectingQuery(new > > SQLTemplate(LiveEvent.class, String.format("INSERT INTO live_events(id, > > time, count) VALUES (%d, %d, 1) ON DUPLICATE KEY UPDATE count = count + 1", > > id, time)); > > > > At the end of the 4000 calls to performNonSelectingQuery I make one call to > > commitChanges(): > > > > dataContext.commitChanges(); > > > > This causes each INSERT statement to run in its own transaction. The logs > > look like this (an example of 2 such transactions): > > > > 173928 [main] INFO org.apache.cayenne.access.QueryLogger - --- will run 1 > > query. > > 173928 [main] INFO org.apache.cayenne.access.QueryLogger - --- transaction > > started. > > 173929 [main] INFO org.apache.cayenne.access.QueryLogger - INSERT INTO > > .... > > 173930 [main] INFO org.apache.cayenne.access.QueryLogger - === updated 3 > > rows. > > 173961 [main] INFO org.apache.cayenne.access.QueryLogger - +++ transaction > > committed. > > 173961 [main] INFO org.apache.cayenne.access.QueryLogger - --- will run 1 > > query. > > 173961 [main] INFO org.apache.cayenne.access.QueryLogger - --- transaction > > started. > > 173962 [main] INFO org.apache.cayenne.access.QueryLogger - INSERT INTO > > .... > > 173963 [main] INFO org.apache.cayenne.access.QueryLogger - === updated 3 > > rows. > > 173994 [main] INFO org.apache.cayenne.access.QueryLogger - +++ transaction > > committed. > > > > This is taking much too long. Is it possible to batch all of the inserts > > into a single database operation to reduce the overhead? > > > > I am using Cayenne 3.0.1 (Java 1.6.0_22). > > > > Any help would be much appreciated. > > > > Thanks, > > > > Marc >
