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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc pgsql-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

Browse pgsql-jdbc by date

  From Date Subject
Next Message John Cavacas 2003-02-16 23:23:23 Re: Good performance?
Previous Message Barry Lind 2003-02-15 02:25:16 Re: new driver questions

Browse pgsql-performance by date

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