Re: How to batch INSERT .. ON DUPLICATE operations

[Andrus Adamchik]

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