FAQ
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 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

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 3 of 4 | next ›
Discussion Overview
groupuser @
categoriescayenne
postedJul 13, '11 at 5:00p
activeJul 13, '11 at 9:58p
posts4
users2
websitecayenne.apache.org

2 users in discussion

Marc O'Morain: 3 posts Andrus Adamchik: 1 post

People

Translate

site design / logo © 2022 Grokbase