FAQ
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

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 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