How to speed-up inserts with jdbc

From: Michael Kleiser <mkl(at)webde-ag(dot)de>
To: Jeff <threshar(at)torgo(dot)978(dot)org>
Cc: Shane|SkinnyCorp <shanew(at)skinnycorp(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: How to speed-up inserts with jdbc
Date: 2004-11-10 13:51:57
Message-ID: 41921CFD.7060604@webde-ag.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jeff 2004-11-10 13:55:01 Re: How to speed-up inserts with jdbc
Previous Message Jeff 2004-11-10 13:04:03 Re: Need advice on postgresql.conf settings