Return-Path: X-Original-To: apmail-cayenne-user-archive@www.apache.org Delivered-To: apmail-cayenne-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 1F5FF6DEA for ; Wed, 13 Jul 2011 17:00:41 +0000 (UTC) Received: (qmail 53212 invoked by uid 500); 13 Jul 2011 17:00:40 -0000 Delivered-To: apmail-cayenne-user-archive@cayenne.apache.org Received: (qmail 53151 invoked by uid 500); 13 Jul 2011 17:00:40 -0000 Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@cayenne.apache.org Delivered-To: mailing list user@cayenne.apache.org Received: (qmail 53143 invoked by uid 99); 13 Jul 2011 17:00:39 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 13 Jul 2011 17:00:39 +0000 X-ASF-Spam-Status: No, hits=2.2 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_NEUTRAL X-Spam-Check-By: apache.org Received-SPF: neutral (nike.apache.org: local policy) Received: from [74.125.83.43] (HELO mail-gw0-f43.google.com) (74.125.83.43) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 13 Jul 2011 17:00:30 +0000 Received: by gwm11 with SMTP id 11so3472633gwm.16 for ; Wed, 13 Jul 2011 10:00:09 -0700 (PDT) MIME-Version: 1.0 Received: by 10.101.186.33 with SMTP id n33mr1274191anp.92.1310576408912; Wed, 13 Jul 2011 10:00:08 -0700 (PDT) Received: by 10.100.229.7 with HTTP; Wed, 13 Jul 2011 10:00:08 -0700 (PDT) X-Originating-IP: [89.101.190.206] Date: Wed, 13 Jul 2011 18:00:08 +0100 Message-ID: Subject: How to batch INSERT .. ON DUPLICATE operations From: "Marc O'Morain" To: user@cayenne.apache.org Content-Type: multipart/alternative; boundary=001636c597eb4de20704a7f659fe X-Virus-Checked: Checked by ClamAV on apache.org --001636c597eb4de20704a7f659fe Content-Type: text/plain; charset=ISO-8859-1 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 --001636c597eb4de20704a7f659fe--