X-Original-To: pgsql-jdbc@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 7E299474E53; Sun, 16 Feb 2003 23:44:58 -0500 (EST) Received: from mail.xythos.com (ip-216-36-77-241.dsl.sjc.megapath.net [216.36.77.241]) by postgresql.org (Postfix) with ESMTP id E70B3474E42; Sun, 16 Feb 2003 23:44:57 -0500 (EST) Received: from ravms by mail.xythos.com with mail-ok (Exim 3.36 #3) id 18kd9K-0003J9-00; Mon, 17 Feb 2003 04:45:02 +0000 Received: from h-66-166-17-184.snvacaid.covad.net ([66.166.17.184] helo=xythos.com) by mail.xythos.com with asmtp (Exim 3.36 #3) id 18kd9K-0003Iy-00; Mon, 17 Feb 2003 04:45:02 +0000 Message-ID: <3E5068CD.4050909@xythos.com> Date: Sun, 16 Feb 2003 20:45:01 -0800 From: Barry Lind User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Rafal Kedziorski Cc: pgsql-jdbc@postgresql.org, pgsql-performance@postgresql.org Subject: Re: Good performance? References: <5.2.0.9.0.20030216232710.01b40810@mail.polonium.de> In-Reply-To: <5.2.0.9.0.20030216232710.01b40810@mail.polonium.de> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Envelope-To: rafcio@polonium.de, pgsql-jdbc@postgresql.org, pgsql-performance@postgresql.org X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/241 X-Sequence-Number: 6412 Rafal, Performance of postgres running under cygwin isn't great. Can you try the same test on a different platform? It also looks like you are running in autocommit mode. You should see a significant performance improvement if you batch your commits in say groups of 1000 inserts per commit. thanks, --Barry Rafal Kedziorski wrote: > Hi, > > I have following tables: > > with id as number(20,0): > CREATE TABLE public.firm ( > firm_id numeric(20, 0) NOT NULL, > name varchar(40) NOT NULL, > CONSTRAINT firm_pkey PRIMARY KEY (firm_id) > ) > > with id as int8: > > CREATE TABLE public.firmint8 ( > firmint8_id int8 NOT NULL, > name varchar(40) NOT NULL, > CONSTRAINT firmint8_pkey PRIMARY KEY (firmint8_id) > ) > > my system: > - dual PIII 800 MHz with 640 MB RAM > - cygwin > - PostgreSQL 7.3.1 (default configuration after install thru cygwin) > - J2SE 1.4.1_01 > - JDBC driver for J2SE 1.4.1_01 and J2SE 1.3.1_06 > > I get very bad performance inserting 1000 simple values in the tables > defined above. I'm using PreparedStatement without Batch. > > with J2SE 1.4.1_01 it need: > > java db.InsertFirmSQLNumber > InsertFirmSQLNumber() needed 74438 for creating 1000 entries > InsertFirmSQLNumber() needed 53140 for creating 1000 entries > > java db.InsertFirmSQLInt8 > InsertFirmSQLInt8() needed 44531 for creating 1000 entries > InsertFirmSQLInt8() needed 63500 for creating 1000 entries > InsertFirmSQLInt8() needed 70578 for creating 1000 entries > InsertFirmSQLInt8() needed 68375 for creating 1000 entries > InsertFirmSQLInt8() needed 80234 for creating 1000 entries > > > with J2SE 1.3.1_06 it need: > > java db.InsertFirmSQLNumber > InsertFirmSQLNumber() needed 40093 for creating 1000 entries > InsertFirmSQLNumber() needed 39016 for creating 1000 entries > InsertFirmSQLNumber() needed 39579 for creating 1000 entries > > java db.InsertFirmSQLInt8 > InsertFirmSQLInt8() needed 75437 for creating 1000 entries > InsertFirmSQLInt8() needed 39156 for creating 1000 entries > InsertFirmSQLInt8() needed 41421 for creating 1000 entries > InsertFirmSQLInt8() needed 41156 for creating 1000 entries > > > and there is the Java code: > > DriverManager.registerDriver(new org.postgresql.Driver()); > Connection conn = DriverManager.getConnection(db, dbuser, > dbpassword); > PreparedStatement pstmt = null; > ResultSet rs = null; > > if (conn != null) { > String query = "insert into firm values(?,?)"; > pstmt = conn.prepareStatement(query); > > long start = System.currentTimeMillis(); > for (int i = 0; i < N; i++) { > pstmt.setLong(1, getUniquelongID()); > pstmt.setString(2, "" + i); > pstmt.executeUpdate(); > } > long end = System.currentTimeMillis() - start; > > System.out.println("InsertFirmSQLInt8() needed " + end + " > for creating " + N + " entries"); > } > > closeConnections(conn, pstmt, rs); > } > > Is this a JDBC driver or PostgreSQL configuration problem? Or is the > performance normal? > > > Best Regards, > Rafal > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >