Performance of jdbc insert statements and select nextval

[ralf(dot)baumhof(at)bgs-ag(dot)de]

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: jdbc_test.zip
Description: Zip archive