Thread: one transaction or multiple inserts?

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

And more importantly, why? :)

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.

Thanks,

Jc

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