From: | Barry Lind <blind(at)xythos(dot)com> |
---|---|
To: | Rafal Kedziorski <rafcio(at)polonium(dot)de> |
Cc: | pgsql-jdbc(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Good performance? |
Date: | 2003-02-17 04:45:01 |
Message-ID: | 3E5068CD.4050909@xythos.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc pgsql-performance |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Rafal Kedziorski | 2003-02-17 09:12:45 | Re: Good performance? |
Previous Message | Yan Yan Che | 2003-02-17 04:21:20 | PrimaryKeyExists Error |
From | Date | Subject | |
---|---|---|---|
Next Message | Rafal Kedziorski | 2003-02-17 09:12:45 | Re: Good performance? |
Previous Message | Rafal Kedziorski | 2003-02-17 00:23:45 | Re: Good performance? |