I don't see the transaction steps, so forgive me if you just didn't show them. But the common knowledge is that you need to do large inserts inside a transaction, and group that in chunks of 1000 lines or so (don't know where 1000 comes from, but people smarter than me have said this). WIthout that, each insert goes through the "begin transaction, do action, commit transaction" overhead, and it's sloooooooww.

Also, if you have indexes, add them after the entire load is complete. No sense spending a lot of work to maintain a index that won't be queried in its current state.

Even cooler, because DDL in PostgreSQL can also be made part of a transaction, you can begin the transaction, create the table, do all the data loading, add any index and triggers, and then finally commit. Any other thread watching the database would see nothing until the final commit, so it'll look like the entire table sprang into existence fully formed! Even Oracle can't do that, I'm told. (Yes, you can roll back a table drop or schema change in PostgreSQL. Cool.)

-- Randal L. Schwartz, Perl hacker
Be sure to read my standard disclaimer if this is a reply.

In reply to Re: Quickest way to insert many rows with DBI by merlyn
in thread Quickest way to insert many rows with DBI by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.