Re: batch inserts are "slow"

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: pgsql-performance-owner@postgresql.org
>[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
>Christopher Kings-Lynne
>Sent: Monday, May 02, 2005 11:11 AM
>To: tim@se.linux.org
>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
>majordomo@postgresql.org
>