Good performance? - Mailing list pgsql-jdbc

From Rafal Kedziorski
Subject Good performance?
Date
Msg-id 5.2.0.9.0.20030216232710.01b40810@mail.polonium.de
Whole thread Raw
Responses Re: Good performance?  (Barry Lind <blind@xythos.com>)
List pgsql-jdbc
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


pgsql-jdbc by date:

Previous
From: "John Cavacas"
Date:
Subject: Re: Exception Identification - What to do with no codes?
Next
From: "John Cavacas"
Date:
Subject: Re: Good performance?