Thread: Performance of jdbc insert statements and select nextval

i found the following problem:

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.

thanks, Ralf.normal {font-family: "Arial";font-size: 8pt;font-style: italic} .fett {font-family: "Arial";font-size: 10pt;font-style: italic;font-weight: bold; color: navy}

---

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

We would probably have to see the code to understand the problem better.

Dave

On Wed, 18 Feb 2009, ralf.baumhof@bgs-ag.de wrote:

> 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?

It's not clear how pgadmin is executing the inserts, but your description
makes it seem like your Java code is doing things a single execution at a
time even if it's only prepared once.  If pgadmin is sending all of these
statements across to the server in one batch, then it has just one network
roundtrip to make, while the Java code will need 20000 roundtrips.  Since
your alternating insert/select nextval, you can't really use batch
execution.  Perhaps you can rework your java code to either fetch a lot of
nextvals at once and then use them in a later batch insert.

SELECT nextval('myseq') FROM generate_series(1,10000);

Or you could rework it to do:

CREATE TABLE tab (a serieal, b int);
INSERT INTO tab (b) VALUES (1), (2), (3) RETURNING a;

Kris Jurka