Performance of jdbc insert statements and select nextval - Mailing list pgsql-jdbc
i found the following problem:
Summary:
10000 inserts with pgadmin tool into one table (primary key bigserial) can be done wihtin 3 seconds. If i perform the same job with jdbc this takes 13 seconds. The insert statement is prepared only once, the statement for fetching the nextval also. If i omit the select nextval execution time improves to 8 seconds. Can anybody tell me why jdbc is 3 to four times slower than pgadmin? I am using Postgresql 8.2 and 8.3 Database server, java 1.5 and 1.6 and i tried the drivers 8.2 and 8.3 in type 3 and type 4. The execution times are always identical.
Precise description:
The problem results from the following application problem. We are developing a web application with geronimo application server, JPA persistence technology and PostgreSQL database server. The application is tested on Ubuntu Linux and Windows XP, with postgresql 8.2 and 8.3 servers on java 1.5 and 1.6 platform. I made a test with "logical" insert of objects which results in 50000 inserts can be done within 2 minutes, what means 416 inserts per second, or 1 insert in 2/1000 sec (2ms). The cost estimate with explain for an insert is on the other hand is 0,4 ms. So, where is the gap? I left JPA and tried jdbc. Here i found out: 10000 inserts in sql script can be done by PGAdmin within 3 seconds, a simple jdbc loader program needs 13 seconds. Leaving the id generation to the database, this can be done within 8 sec (but in my jpa world i of course need the id, and there was a severe problem of leaving this task to the jpa model. The automatic id generation (which of course is supported by jpa) did not work in postgresql if the tables are in a schema).
This is my jdbc test program code:
Yet now i have tried to other database servers. MySql GA and Oracle XE (10g Express). Both servers lead to the same performance (10000 inserts within 13 seconds). So, PostgreSQL is not less or more performant than others. Following a hint from Graig Ringer. May it be that a firewall or virus scanner forces the performance slow down? This perhaps might explain why the system does not scale with faster hardware.
Thanks, Ralf
| BGS Beratungsgesellschaft Software Systemplanung AG | ||||
| Niederlassung Nord Ebertstra�e 21 26382 Wilhelmshaven Fon: +49 (0) 4421 / 9683-700 Fax: +49 (0) 4421 / 9683-790 www.bgs-ag.de | Gesch�ftssitz Mainz Registergericht Amtsgericht Mainz HRB 62 50 | Aufsichtsratsvorsitzender Klaus Hellwig Vorstand Hanspeter Gau Hermann Kiefer Nils Manegold |   |
Attachment
pgsql-jdbc by date: