Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
Use DBI and insert the values as I parse each line

Inserting records one by one is going to be the slowest approach.

However, if you can use the extended insertion syntax that MySQL supports, this could be one of the best solutions.

An extended insertion query looks like this:

INSERT INTO tablename (col1, col2, col3) VALUES (value1, value2, value3), (value1, value2, value3), (value1, value2, value3), (value1, value2, value3);

The bad news is that you need to figure out - with some benchmarks - how many records you should pass to each query, considering the limits set by max_allowed_packet (1 MB by default).

When I had to do something similar, with a max_allowed_packet of 3 MB, I had very good results building queries up to 2 MB. As for the technique to build these queries, there is a detailed example in Speeding up the DBI, but the basic idea is like the following:

my @values = ( ["A", "B", "D", "0"], ["E", "F", "G", "1"], ["H", "I", "J", "2"], ["K", "L", "M", "3"], ["N", "O", "P", "4"], ["Q", "R", "S", "5"], ["T", "U", "V", "6"], ["W", "X", "Y", "7"], ); my $query = "INSERT INTO tablename VALUES \n" . join(",\n", ( map { "(". join ( ", ", map {$dbh->quote($_)} @$_). ")" } @values));
Parse the values into a delimited file and import this using mysqlimport ?

This is also feasible. mysqlimport is one of the most tested tools in MySQL suite, and it is very much efficient, especially if dealing with tab-delimited files. Personally, once I found the right balance with the extended insert, I almost always manage to outperform this method using multi-record queries. YMMV.

Other wisdom ?

Yes. If your table has some indexes, in addition to primary key or unique keys, then you can use this set of instructions before and after the insertion:

ALTER TABLE tablename DISABLE KEYS; # do the insertion ALTER TABLE tablename ENABLE KEYS;

This trick will speed up the insertion considerably. Instead of building the indexes row by row, it will do it all at once at the end of the insertion.

 _  _ _  _  
(_|| | |(_|><

In reply to Re: DBI or mysqlimport? by gmax
in thread DBI or mysqlimport? by js1

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, 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, 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.
  • Log In?

    What's my password?
    Create A New User
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others drinking their drinks and smoking their pipes about the Monastery: (5)
    As of 2021-03-08 15:05 GMT
    Find Nodes?
      Voting Booth?
      My favorite kind of desktop background is:

      Results (125 votes). Check out past polls.