Re: Performance of jdbc insert statements and select nextval

[Kris Jurka <books(at)ejurka(dot)com>]

On Wed, 18 Feb 2009, ralf(dot)baumhof(at)bgs-ag(dot)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