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
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 |