Rép. : Re: one transaction or multiple inserts?

I recently ran a simple test about that:
- inserting N rows was 17x faster in a single transaction (24s for 20000 rows) than without explicitly specifying it (6min55s), which results in N transactions.
- Copy is MUCH faster: around 2s only for 20000 rows on my Pentium II box (Redhat7.3, pg 7.2.2).

Erwan

( Shrindar, sorry if you already received this directly. It's my 1st post on this forum...)

-------------------------------------------------------------------------------
Erwan DUROSELLE // SEAFRANCE DSI
Responsable Bases de Données // Databases Manager
Tel: +33 (0)1 55 31 59 70 // Fax: +33 (0)1 55 31 85 28
email: eduroselle(at)seafrance(dot)fr
-------------------------------------------------------------------------------

>>> "Shridhar Daithankar" <shridhar_daithankar(at)persistent(dot)co(dot)in> 03/10/2002 11:18 >>>
On 3 Oct 2002 at 18:07, Jean-Christian Imbeault wrote:

> When inserting a lot of data into a DB which would be faster, doing all
> the inserts in one transaction or doing multiple inserts?

One transaction with multiple inserts
>
> And more importantly, why? :)

Because when transaction is committed WAL is synced in some way depending upon
configuration and that costs time as it's disk activity which is much slower
compared to rest of the system..

> I'M assuming that each insert is in fact a transaction and doing, for
> example, 1000 inserts is the same as doing 1000 transaction. But if I
> put them all in one transaction, it would be equivalent to doing (duh) 1
> transaction.

Correct..

Use copy. That's simpler but it puts everythig in one transaction. You wouldn't
want to load a 100GB dump with that..

Bye
Shridhar

--
Accuracy, n.: The vice of being right

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly