Re: Performance comparison to psql.

Michael,

Try passing the "-1" (one) flag to psql--this processes the script file
as a single transaction. Otherwise, it indeed treats each statement as a
single implicit transaction.

--
Maciek Sakrejda
Truviso, Inc.
http://www.truviso.com

-----Original Message-----
From: Michael Nacos <m(dot)nacos(at)gmail(dot)com>
To: Kris Jurka <books(at)ejurka(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] Performance comparison to psql.
Date: Thu, 25 Sep 2008 17:01:44 +0100

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

Dear Kris,

thanks for the link 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