Обсуждение: 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 ....