pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Barry Lind <blind@xythos.com>
To: Rafal Kedziorski <rafcio@polonium.de>
Cc: pgsql-jdbc@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: Good performance?
Date: Sun, 16 Feb 2003 20:45:01 -0800
Message-ID: <3E5068CD.4050909@xythos.com> (raw)
In-Reply-To: <5.2.0.9.0.20030216232710.01b40810@mail.polonium.de>
References: <5.2.0.9.0.20030216232710.01b40810@mail.polonium.de>

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
> 





view thread (11+ messages)  latest in thread

Message-ID: <3E5068CD.4050909@xythos.com>
Permalink:  ../3E5068CD.4050909@xythos.com/
Also on:    postgresql.org/message-id/3E5068CD.4050909@xythos.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: blind@xythos.com, rafcio@polonium.de, pgsql-jdbc@postgresql.org
  Subject: Re: Good performance?
  In-Reply-To: <3E5068CD.4050909@xythos.com>
  References: <5.2.0.9.0.20030216232710.01b40810@mail.polonium.de> <3E5068CD.4050909@xythos.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox