cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Marc O'Morain" <marc.omor...@swrve.com>
Subject Re: How to batch INSERT .. ON DUPLICATE operations
Date Wed, 13 Jul 2011 19:35:25 GMT
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 <andrus@objectstyle.org>
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
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message