Re: Performance of jdbc insert statements and select nextval

From: Kris Jurka <books(at)ejurka(dot)com>
To: ralf(dot)baumhof(at)bgs-ag(dot)de
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Performance of jdbc insert statements and select nextval
Date: 2009-02-20 17:41:52
Message-ID: Pine.BSO.4.64.0902201230530.23634@leary.csoft.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Maciek Sakrejda 2009-02-20 18:10:59 Re: COPY support
Previous Message Kris Jurka 2009-02-20 17:29:39 Re: SSL - Providing client certificates