Re: Performance comparison to psql.

branching off from
http://archives.postgresql.org/pgsql-jdbc/2008-09/msg00074.php and joining
this thread

Dear Kris,

thanks for the link<http://archives.postgresql.org/pgsql-jdbc/2008-02/msg00018.php>in
regards to COPY versus JDBC batch. From the numbers in that email, I
am
tempted to assume that, if your tables have indices, you would expect
something like *1x* speed of *COPY* = *6x* speed of *JDBC batch* =
*36x*speed of
*single-statement JDBC
*

Comparison table (records inserted per millisecond)
COPY JDBC JDBC batch
WITHOUT INDEXES: 198 1.5 14
WITH 2 INDEXES: 45 1.5 10

In the last few weeks, I have been trying to improve a bulk update process
which involves arbitrary SQL statements, so a COPY command has not been an
option. I cannot really compare what I've been doing to the performance of a
COPY command, cause the specs differ, but I can compare pgBee's performance
to psql's processing text files of SQL statements.

For the same input file, psql took 112m, whereas pgBee finished in 21m. Both
were run on the same database server (localhost), pgBee was batching groups
of 100 statements at a time and a real data file was used, with 1131753 SQL
statements in total (511335 DELETEs and 567577 INSERTs). pgBee seems to be
doing 898 operations/millisecond, or ~450 INSERTSs/millisecond ! Is that
possible? psql seems to manage 168 operations/millisecond - half of those
are INSERTs.

I suppose I am sort of doing what a JDBC COPY command would do (but with
arbitrary SQL statements): pgBee handles the parsing of files, takes care of
encoding issues, escapes some characters and find and tries to execute all
statements in the most efficient way. Somehow, reading lines from a text
file into a String buffer, splitting the statements up and executing them as
a batch gives you a real performance boost. Does psql implicitly handle each
statetement as a separate transaction? This could account for pgBee's better
performance.

Michael