Re: jdbc batch performance problem

Hi Dave,
thanks for the answer.

Dave Cramer wrote:

> Hi,
>
> You can try dropping the index before doing the inserts and rebuilding
> it afterwards, that may help.

I make this test. It is not better.As I wrote if I use insert into mytable
select ... (with index) the time is 10 ms.
Of this reason I think the problem is only by jdbc implementation, not by data
base.

> On Sat, 2003-07-19 at 02: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.
> this is not comparing apples to apples, the insert into ... does
> everything on the backend, jdbc has to get the data to the server.
> >
> > Exist any basic performance problem for pg jdbc or I make mistake?
> > Also I testet the same example on oracle 8i and mysql.
>
> How was this test done? For instance did you use transactions with
> mysql? did you use their jdbc interface, was the connection to the
> database the same speed in each case?
>

I am not mysql expert. I used the standart setup (with transactions). All the
tests I make on the same computer:dual PIII 1 GHz, 4 GB RAM, linux 7.3.
I make this tests only to be sure that the problem is not into JVM or my app.
server.
I expected to reach 2000 (or 3000) inserts/sec. This is the result by oracle
and mysql.
With pg I got ~ 300 inserts/sec.
It is 10 time slower and I do not think it is normal.
I know pg relativ good and think that th db performance is aprox the same as
oracle.
And for me the problem is into the jdbc driver.

Any idea?
ivan.

> > It is working much much better.
> > I love pg but it will be great if I can make insert faster.
> >
> > Many thanks,
> > ivan.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
> --
> Dave Cramer <Dave(at)micro-automation(dot)net>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)