We ran into the need to use COPY, but our application is also in Java. We wrote a JNI bridge to a C++ routine that uses the libpq library to do the COPY. The coding is a little bit weird, but not too complicated - the biggest pain in the neck is probably getting it into your build system.
Here's the Java tutorial on JNI: http://java.sun.com/docs/books/tutorial/native1.1/concepts/index.html Hope that helps! - DAP >-----Original Message----- >From: [EMAIL PROTECTED] >[mailto:[EMAIL PROTECTED] On Behalf Of >Christopher Kings-Lynne >Sent: Monday, May 02, 2005 11:11 AM >To: [EMAIL PROTECTED] >Cc: pgsql-performance@postgresql.org >Subject: Re: [PERFORM] batch inserts are "slow" > >> conn.setAutoCommit(false); >> pst = conn.prepareStatement("INSERT INTO tmp (...) VALUES >(?,?)"); for >> (int i = 0; i < len; i++) { >> pst.setInt(0, 2); >> pst.setString(1, "xxx"); >> pst.addBatch(); >> } >> pst.executeBatch(); >> conn.commit(); >> >> This snip takes 1.3 secs in postgresql. How can I lower that? > >You're batching them as one transaction, and using a prepared >query both of which are good. I guess the next step for a >great performance improvement is to use the COPY command. >However, you'd have to find out how to access that via Java. > >I have a nasty suspicion that the release JDBC driver doesn't >support it and you may have to apply a patch. > >Ask on pgsql-jdbc@postgresql.org perhaps. > >Chris > >---------------------------(end of >broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to >[EMAIL PROTECTED] > ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
