John Holland wrote:
>
> Hello -
>
> I've been lurking on this list for a little while. I have just done a
> little tinkering with Postgres 6.4.2, comparing it to Oracle and mySQL on
> Linux. It would appear that just doing a lot of inserts (ie 40000) in a
> loop is enormously slower in Postgres in two ways that I tried it.
> One - using a loop in Java that makes a JDBC call to insert.
> Two- using plpgsql as a comparision to PL/SQL.
>
> Perhaps these are bad techniques and a C based proc would do better?
>
> I really like the idea of an open source DB and am impressed with a lot I
> see about PostgreSQL - however the speed difference is pretty bad -
> 4.5 minutes versus about 20 seconds.
>
> Is that just the way it goes? Are there options that would make it faster
> that are not the defaults?
Oracle uses chained transaction mode (i.e. - all queries run in
single transaction untill explicit COMMIT/ABORT), MySQL hasn't
transaction at all...
PostgreSQL uses un-chained transaction mode: each query runs
in own transaction - 40000 transaction commits !!!
Try to use BEGIN/END to run all inserts in single transaction
and please let us know results.
Vadim