pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Kleiser <mkl@webde-ag.de>
To: Jeff <threshar@torgo.978.org>
Cc: Shane|SkinnyCorp <shanew@skinnycorp.com>
Cc: pgsql-performance@postgresql.org
Subject: How to speed-up inserts with jdbc
Date: Wed, 10 Nov 2004 14:51:57 +0100
Message-ID: <41921CFD.7060604@webde-ag.de> (raw)
In-Reply-To: <FF2CC26B-3318-11D9-9406-000D9366F0C4@torgo.978.org>
References: <003201c4c68e$8d347ee0$a702a8c0@shanepc>
	<FF2CC26B-3318-11D9-9406-000D9366F0C4@torgo.978.org>

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



view thread (7+ messages)  latest in thread

Message-ID: <41921CFD.7060604@webde-ag.de>
Permalink:  ../41921CFD.7060604@webde-ag.de/
Also on:    postgresql.org/message-id/41921CFD.7060604@webde-ag.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mkl@webde-ag.de, threshar@torgo.978.org, shanew@skinnycorp.com
  Subject: Re: How to speed-up inserts with jdbc
  In-Reply-To: <41921CFD.7060604@webde-ag.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox