Re: Good performance? - Mailing list pgsql-jdbc
From | Barry Lind |
---|---|
Subject | Re: Good performance? |
Date | |
Msg-id | 3E5068CD.4050909@xythos.com Whole thread Raw |
In response to | Good performance? (Rafal Kedziorski <rafcio@polonium.de>) |
Responses |
Re: Good performance?
|
List | pgsql-jdbc |
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 >
pgsql-jdbc by date: