copy vs. insert w/ no autocommit
copy vs. insert w/ no autocommit
От:
Matthew Kennedy <mkennedy@opushealthcare.com>
Дата:
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
Re: copy vs. insert w/ no autocommit
От:
Francisco Reyes <lists@natserv.com>
Дата:
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.
