Thread: COPY versus INSERT
I'm loading a database with a lot of data from an external source (I'm convertin all my data to postgresql) Right now I'm just connecting to the database and issuing a whole lot of INSERTS and this is taking longer than I had planned. Would it be faster to just dump my database ina tab delimited format and use a COPY FROM or psql /copy command? Thanks Take care, Jay
"Jay O'Connor" <joconnor@cybermesa.com> writes: > I'm loading a database with a lot of data from an external source (I'm > convertin all my data to postgresql) > > Right now I'm just connecting to the database and issuing a whole lot of > INSERTS and this is taking longer than I had planned. > > Would it be faster to just dump my database ina tab delimited format and > use a COPY FROM or psql /copy command? Most definitely--the SQL parser and planner have to be run for each INSERT. The parsing done by COPY is much simpler. -Doug
On Tue, 13 May 2003, Jay O'Connor wrote: > I'm loading a database with a lot of data from an external source (I'm > convertin all my data to postgresql) > > Right now I'm just connecting to the database and issuing a whole lot of > INSERTS and this is taking longer than I had planned. > > Would it be faster to just dump my database ina tab delimited format and > use a COPY FROM or psql /copy command? Speed wise, here's my take on what's faster: copy = X time. insert 10,000 rows at a time in transactions: 2-10X time insert all rows in autocommit, i.e. single transactions: (10^100)X Of course, that last one is an exageration, but honestly, when you're waiting on it, it feels that way. :-)
On Wednesday 14 May 2003 3:23 am, Jay O'Connor wrote: > I'm loading a database with a lot of data from an external source (I'm > convertin all my data to postgresql) > > Right now I'm just connecting to the database and issuing a whole lot of > INSERTS and this is taking longer than I had planned. > > Would it be faster to just dump my database ina tab delimited format and > use a COPY FROM or psql /copy command? Almost always. Might want to try batching your INSERTs into transactions of a few hundred-few thousand first though - that frequently helps a lot: BEGIN; INSERT ...1 INSERT ...2 ... INSERT ...999 COMMIT; -- Richard Huxton
> Almost always. > > Might want to try batching your INSERTs into transactions of a few hundred-few > thousand first though - that frequently helps a lot: > > BEGIN; > INSERT ...1 > INSERT ...2 > ... > INSERT ...999 > COMMIT; > I have a perl script, that runs a loop, in which it executes a prepared INSERT INTO query... the loop takes about 1 - 2 seconds... does BEGIN; .... COMMIT; also apply to some PREPARE-routines?
"alex b." <mailinglists1@gmx.de> writes: > I have a perl script, that runs a loop, in which it executes a > prepared INSERT INTO query... > > the loop takes about 1 - 2 seconds... > > does BEGIN; .... COMMIT; also apply to some PREPARE-routines? If you have autocommit turned on (which I believe is the default for DBD::Pg) each INSERT will cause a transaction commit, whether the query is prepared or not. -Doug
alex b. wrote: > >> Almost always. >> >> Might want to try batching your INSERTs into transactions of a few >> hundred-few thousand first though - that frequently helps a lot: >> >> BEGIN; >> INSERT ...1 >> INSERT ...2 >> ... >> INSERT ...999 >> COMMIT; >> > > I have a perl script, that runs a loop, in which it executes a prepared > INSERT INTO query... > > the loop takes about 1 - 2 seconds... > > does BEGIN; .... COMMIT; also apply to some PREPARE-routines? well, I just tried it, YES IT DOES ALSO APPLY ....