Thread: faster inserting from dump?

faster inserting from dump?

From
"chris markiewicz"
Date:
hello.

how can i increase the speed of building a database from a dump?  there are
currently many tens-of-thousands of insert statements...i assume that a
commit is run after each statement?  i am guessing that it would be faster
if i commit after every hundred or thousand?  is there an easy way to tell
psql to do this?

please respond directly to me if it is not inconvenient.

thanks
chris


Re: faster inserting from dump?

From
"Gregory Wood"
Date:
> how can i increase the speed of building a database from a dump?  there
are
> currently many tens-of-thousands of insert statements...i assume that a
> commit is run after each statement?

Correct... every statement is wrapped in an implicit transaction if an
explicit one is not provided.

> i am guessing that it would be faster
> if i commit after every hundred or thousand?

Correct again.

> is there an easy way to tell psql to do this?

Just use BEGIN; and COMMIT; to denote a transaction block. That's it!

You might also want to research the COPY command for doing bulks imports. I
personally use INSERTs because I need to have my importing error checked,
but if you can guarantee the integrity of the import, COPY is your best bet.

Greg