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

couple of things

1) That is a fairly old version of postgres, there are considerable 
performance improvements in the last 2 releases since, and even more in 
the pending release.
2) If you are going to insert more rows than that, consider dropping the 
index before, and recreating after the insert.

Dave

Michael Kleiser wrote:

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561




view thread (7+ messages)  latest in thread

Message-ID: <41922031.4000303@fastcrypt.com>
Permalink:  ../41922031.4000303@fastcrypt.com/
Also on:    postgresql.org/message-id/41922031.4000303@fastcrypt.com

 · 

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: pg@fastcrypt.com, mkl@webde-ag.de, threshar@torgo.978.org, shanew@skinnycorp.com
  Subject: Re: How to speed-up inserts with jdbc
  In-Reply-To: <41922031.4000303@fastcrypt.com>

* 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