Thread: copy vs. insert w/ no autocommit

I realize a COPY statement is much faster than individual INSERT
statements. But how does COPY compare to individual INSERTS within a
BEGIN/END or with setAutoCommit(false)? If there's a difference, are we
talking orders-of-magnitude in difference? (I am using JDBC and my
design might be affected depending on whether COPY is faster than a
prepared INSERT w/ auto-commit off.)

Matt

On 18 Dec 2001, Matthew Kennedy wrote:

> I realize a COPY statement is much faster than individual INSERT
> statements.

How? Have you tested it? Or do you mean that from reading people's
comments you believe this to be "common knowledge".

>But how does COPY compare to individual INSERTS within a
>BEGIN/END or with setAutoCommit(false)?

Are we talking "copy" or "\copy"?

I am not sure about \copy, but "copy" as far as I know doesn't update all
the stats/indexes so it should be significantly faster to use a copy.

So far for all my big jobs (anything greater than a few thousand rows) I
always use copy and it is fairly fast.

Have you tried doing some testing? How many indexes do you have on the
table?

> If there's a difference, are we
> talking orders-of-magnitude in difference?

Depending on your disk subsistem I would expect copy should be several
times faster due to index overhead.