How to speed-up inserts with jdbc - Mailing list pgsql-performance

From Michael Kleiser
Subject How to speed-up inserts with jdbc
Date
Msg-id 41921CFD.7060604@webde-ag.de
Whole thread Raw
In response to Re: Need advice on postgresql.conf settings  (Jeff <threshar@torgo.978.org>)
Responses Re: How to speed-up inserts with jdbc  (Jeff <threshar@torgo.978.org>)
Re: How to speed-up inserts with jdbc  (Dave Cramer <pg@fastcrypt.com>)
Re: How to speed-up inserts with jdbc  (Edwin Eyan Moragas <haaktu@gmail.com>)
List pgsql-performance
Im PostgreSQL 7.2.2 / Linux 2.4.27  dual-processor Pentium III 900MHz,

we have this table:

create table testtable (id SERIAL PRIMARY KEY, coni VARCHAR(255), date TIMESTAMP, direction VARCHAR(255), partner
VARCHAR(255),type VARCHAR(255), block VARCHAR(255) ); 


We using Java with JDBC-driver pg72jdbc2.jar


our Java-testgrogram is :


public class Stresser implements Runnable {
     public void run() {
         System.out.println("-> start");
         try {

            Class.forName("org.postgresql.Driver");
            Connection con =
DriverManager.getConnection("jdbc:postgresql://"+prop.getProperty("host")+":"+prop.getProperty("port")+"/"+prop.getProperty("dbname"),
prop.getProperty("user"), 
prop.getProperty("pwd"));
            con.setAutoCommit(true);
            Statement  st = con.createStatement();
            java.sql.Timestamp datum = new java.sql.Timestamp(new Date().getTime());
            Date start = new Date();
            System.out.println(start);
            for (int i=0; i<100; ++i) {
                st.executeUpdate("insert into history(uuid,coni,date,direction,partner,type)
values('uuid','content','"+datum+"','dir','partner','type')");
            }
            Date end = new Date();
            System.out.println(end);
            con.close();
         } catch (Exception e) {
             System.out.println("Exception!");
             e.printStackTrace();
         }
         System.out.println("-> ende");
     }

     public static void main(String[] args) {

         for (int i=0; i<10; ++i) {
             Stresser s = new Stresser();
             Thread t = new Thread(s);
             t.start();
         }
     }
}


It is trunning in in 10 Threads. Each thread makes 100 Inserts:

For the 1000 Inserts (10 threads a 100 inserts)
we need 8 seconds.
That's 125 Insets / Seconds.

How could we make it faster ?

Inserting 1000 rows via  INSERT AS SELECT is much faster.

regards
   Michael

pgsql-performance by date:

Previous
From: Jeff
Date:
Subject: Re: Need advice on postgresql.conf settings
Next
From: Jeff
Date:
Subject: Re: How to speed-up inserts with jdbc