Skip site navigation (1) Skip section navigation (2)

Good performance?

From: Rafal Kedziorski <rafcio(at)polonium(dot)de>
To: pgsql-jdbc(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org
Subject: Good performance?
Date: 2003-02-16 23:03:01
Message-ID: 5.2.0.9.0.20030216232710.01b40810@mail.polonium.de (view raw or flat)
Thread:
Lists: pgsql-jdbcpgsql-performance
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 


Responses

pgsql-performance by date

Next:From: John CavacasDate: 2003-02-16 23:23:23
Subject: Re: Good performance?
Previous:From: Mariusz CzuładaDate: 2003-02-16 20:27:31
Subject: Re: Views with unions

pgsql-jdbc by date

Next:From: John CavacasDate: 2003-02-16 23:23:23
Subject: Re: Good performance?
Previous:From: Barry LindDate: 2003-02-15 02:25:16
Subject: Re: new driver questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group