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 How to batch INSERT .. ON DUPLICATE operations
Date Wed, 13 Jul 2011 17:00:08 GMT
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