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.