On Thu, 2002-03-14 at 02:30, Zeugswetter Andreas SB SD wrote:
>
> > So this finaly makes the batch work taking 300% the time Oracle needs.
> > We clearly see our ECPG programs waits for PostgreSQL in the functions
> > were CURSORs are opened. Then, we know the problem is not in ECPG but in
> > PG backend.
>
> > This is unaceptable for our customer. Many batches are launched during
> > the night and have to be completed in 5h (between 0h and 5h). With a
> > ratio of 3, this is not worth think about migration anymore :-(
>
> So why exactly can you not simply do the whole batch in one transaction ?
>
> Unless you need to run concurrent vacuums,
I ran some tests based on their earlier description and concurrent
vacuums (the new, non-locking ones) are a must, best run every few
seconds, as without them the ratio of dead/live tuples will be huge and
that will bog down the whole process.
> or are low on disk space, or need
> to concurrently update the affected rows (thus fear deadlocks or locking out
> interactive clients that update), there is no need to do frequent commits in
> PostgreSQL for batch work.
I also suspect (from reading their description) that the main problem of
parsing/optimising each and every similar query will remain even if they
do run in one transaction.
In my tests of simple updates I got 3/2 speed increase (from 1050 to
1500 updates/sec) by using prepared statements inside a stored procedure
--------------------
Hannu
