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 E70153A4030 for ; Wed, 10 Nov 2004 13:52:35 +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 53787-02 for ; Wed, 10 Nov 2004 13:52:31 +0000 (GMT) Received: from mailgate3.cinetic.de (mailgate3.cinetic.de [217.72.192.164]) by svr1.postgresql.org (Postfix) with ESMTP id BCE493A4084 for ; Wed, 10 Nov 2004 13:52:32 +0000 (GMT) Received: from exchange02.int.cinetic.de (exchange02.int.cinetic.de [10.2.1.12]) by mailgate3.cinetic.de (8.11.6p2/8.11.2/SuSE Linux 8.11.0-0.4) with ESMTP id iAADpvK16412; Wed, 10 Nov 2004 14:51:57 +0100 Received: from [10.1.8.177] ([10.1.8.177]) by exchange02.int.cinetic.de with Microsoft SMTPSVC(6.0.3790.211); Wed, 10 Nov 2004 14:51:57 +0100 Message-ID: <41921CFD.7060604@webde-ag.de> Date: Wed, 10 Nov 2004 14:51:57 +0100 From: Michael Kleiser Reply-To: mkl@webde-ag.de Organization: WEB.DE AG User-Agent: Mozilla/5.0 (X11; U; Linux i686; de-AT; rv:1.7.2) Gecko/20040803 X-Accept-Language: de, , de-de, de-at, en, en-us MIME-Version: 1.0 To: Jeff Cc: Shane|SkinnyCorp , pgsql-performance@postgresql.org Subject: How to speed-up inserts with jdbc References: <003201c4c68e$8d347ee0$a702a8c0@shanepc> In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 10 Nov 2004 13:51:57.0790 (UTC) FILETIME=[71F9C7E0:01C4C72C] 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/170 X-Sequence-Number: 9124 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