That worked great. Thanks for the help.
On Wednesday, July 13, 2011, Marc O'Morain <[email protected]> wrote: > 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 >> > >
