Re: Performance of jdbc insert statements and select nextval - Mailing list pgsql-jdbc

From ralf.baumhof@bgs-ag.de
Subject Re: Performance of jdbc insert statements and select nextval
Date
Msg-id OF8E3F2ACF.B6DCA646-ONC1257566.004A001D-C1257566.004A24AD@LocalDomain
Whole thread Raw
In response to Re: Performance of jdbc insert statements and select nextval  (Kris Jurka <books@ejurka.com>)
List 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@ejurka.com>
Gesendet von: pgsql-jdbc-owner@postgresql.org

20.02.2009 18:59

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







On Fri, 20 Feb 2009, ralf.baumhof@bgs-ag.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

.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
BGS Systemplanung AG

 Ein Unternehmen der nextevolution consulting group

pgsql-jdbc by date:

Previous
From: Guillaume Cottenceau
Date:
Subject: Re: SSL - Providing client certificates
Next
From: "Saleem EDAH-TALLY"
Date:
Subject: Re: SSL - Providing client certificates