Re: jdbc batch performance problem - Mailing list pgsql-jdbc

Hi,
my configuration:
dual PIII 1 GHz, 4 GB RAM, linux red hat 7.3,
40 GB HDD ATA100,
PG 7.3.1
max_connections 256
shared_buffers = 512
sort_mem = 10000

I tested before some time the executeUpdate and executeBatch and found big
diference
by performance.

If as you say it do not exist any diference I think it will be possible to improve

executeBatch.
For example for it's implementation to use the pg copy command.
Exist at the moment any work for improving the batch commands?

regards,
ivan.

Paul Thomas wrote:

> 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   |
> +------------------------------+---------------------------------------------+
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)