Re: jdbc batch performance problem

On 19/07/2003 07:52 pginfo wrote:
> Hi,
>
> I am using pg + jdbc for j2ee development from a long time.
> The main problem I found is the very bad insert performance.
> I am using addBatch and executeBatch for minimum network trafik,
> but it is also very slow.
> For example if I try to insert 1000 rows ( in one transaction) into very
> simple table
> it takes ~ 3000 ms. If i use insert into myTable select ... it takes
> only 10 ms.
>
> Exist any basic performance problem for pg jdbc or I make mistake?
> Also I testet the same example on oracle 8i and mysql.
> It is working much much better.
> I love pg but it will be great if I can make insert faster.
>

You don't say which version of PG you're using or what your hardware/OS
setup is. Anyway, I've been doing some testing of my own as well doing
some digging into the JDBC source. Here is my set up

1GZ Athlon
512MB RAm
30MB ATA66 IDE drive
RedHat Linux 7.2
PostgreSQL 7.3.3
postgresql.conf changes:
shared)buffer = 1000
max_connections = 100
sort_memory = 1024

My test program generates approx 1.2 million records into a table with a
single index. I didn't use addBatch()/executeBatch() but tried variations
of Statement and PreparedStatement in a transaction, commiting
approximately every 100 records. If you look at the source you will see
that executeBatch performs a series of exeucte()'s - one for each
statement added with addBatch() so I don't think there would be any less
network traffic compared to executing each query individually. The results
I got were as follows

1) Using Statement: 290 inserts per second

2) Using PreparedStatement: 420 inserts per second

3) Using PreparedStatement with server prepared statement: 1030 inserts
per second

HTH

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+