Thread: 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