Skip site navigation (1) Skip section navigation (2)

Re: Performance of jdbc insert statements and select nextval

From: ralf(dot)baumhof(at)bgs-ag(dot)de
To: pgsql-jdbc(at)postgresql(dot)org
Cc: Kris Jurka <books(at)ejurka(dot)com>
Subject: Re: Performance of jdbc insert statements and select nextval
Date: 2009-02-23 13:45:02
Message-ID: OF8E3F2ACF.B6DCA646-ONC1257566.004A001D-C1257566.004A24AD@LocalDomain (view raw or flat)
Thread:
Lists: pgsql-jdbc
thanks for your hint.

i have tested this, and you'r right. Packaging my requests into chunks of 
1000 each, i can execute 10000 inserts within 2 seconds. This is exactly 
the performance of  PGAdmin Tool and 5 times faster then performing an 
execute per each object. By the way, when i was talking about explain i 
was thinking of explain analyse and this statement produces a total 
runtime in milliseconds. So the execution time of 0,2 ms per insert is 
close to the cost estimate of explain (analyse). 

Thanks for your help,
Ralf






Kris Jurka <books(at)ejurka(dot)com> 
Gesendet von: pgsql-jdbc-owner(at)postgresql(dot)org
20.02.2009 18:59

An
ralf(dot)baumhof(at)bgs-ag(dot)de
Kopie
pgsql-jdbc(at)postgresql(dot)org
Thema
Re: [JDBC] Performance of jdbc insert statements and select nextval








On Fri, 20 Feb 2009, ralf(dot)baumhof(at)bgs-ag(dot)de wrote:

> [insert with pgadmin is faster than JDBC]

As I explained previously I believe pgadmin is sending all of your inserts 

in one network roundtrip rather than a single insert at a time.  So for 
example:

StringBuffer sql = new StringBuffer();
for (int i=0; i<100; i++) {
                 sql.append("INSERT INTO mytable (a) VALUES (");
                 sql.append(i).append(")");
}
Statement.execute(sql.toString())

will be faster than

for (int i=0; i<100; i++) {
                 String sql = "INSERT INTO mytable (a) VALUES (" + i + 
")";
                 Statement.execute(sql);
}

because the second sample has to make 100 network trips instead of just 
one.

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

Explain cost estimates do not have units of milliseconds, so the 
comparison is invalid.

Kris Jurka

-- 
Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc


BGS Beratungsgesellschaft 
Software Systemplanung AG         Niederlassung Nord 
Ebertstrae 21 
26382 Wilhelmshaven 
Fon: +49 (0) 4421 / 9683-700 
Fax: +49 (0) 4421 / 9683-790 
www.bgs-ag.de Geschftssitz Mainz 
Registergericht 
Amtsgericht Mainz 
HRB 62 50 
  Aufsichtsratsvorsitzender 
Klaus Hellwig 
Vorstand 
Hanspeter Gau 
Hermann Kiefer 
Nils Manegold 


  

In response to

pgsql-jdbc by date

Next:From: Saleem EDAH-TALLYDate: 2009-02-23 14:53:02
Subject: Re: SSL - Providing client certificates
Previous:From: Guillaume CottenceauDate: 2009-02-23 11:25:40
Subject: Re: SSL - Providing client certificates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group