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
