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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Saleem EDAH-TALLY | 2009-02-23 14:53:02 | Re: SSL - Providing client certificates |
Previous Message | Guillaume Cottenceau | 2009-02-23 11:25:40 | Re: SSL - Providing client certificates |