X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C27C73A411A for ; Wed, 10 Nov 2004 14:04:40 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 57250-05 for ; Wed, 10 Nov 2004 14:04:29 +0000 (GMT) Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by svr1.postgresql.org (Postfix) with SMTP id 6612D3A4090 for ; Wed, 10 Nov 2004 14:04:31 +0000 (GMT) Received: (qmail 16596 invoked from network); 10 Nov 2004 14:04:29 -0000 Received: from dcdsl.ebox.com (HELO ?192.168.1.36?) (davec@64.7.143.116) by net2.micro-automation.com with SMTP; 10 Nov 2004 14:04:29 -0000 Message-ID: <41922031.4000303@fastcrypt.com> Date: Wed, 10 Nov 2004 09:05:37 -0500 From: Dave Cramer Reply-To: pg@fastcrypt.com Organization: Postgres International User-Agent: Mozilla Thunderbird 0.8 (X11/20041001) X-Accept-Language: en-us, en MIME-Version: 1.0 To: mkl@webde-ag.de Cc: Jeff , Shane|SkinnyCorp , pgsql-performance@postgresql.org Subject: Re: How to speed-up inserts with jdbc References: <003201c4c68e$8d347ee0$a702a8c0@shanepc> <41921CFD.7060604@webde-ag.de> In-Reply-To: <41921CFD.7060604@webde-ag.de> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/172 X-Sequence-Number: 9126 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