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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
