Edit: Figured the problem itself out, i was misusing the bind command as it is used to bind arrays of data to the columns, instead of datasets. If anyone has speed suggestions, i'd still like to hear 'em though.



Greetings monks,

I am trying to do a large number of inserts (30 chunks with ~200_000 inserts each) into an SQLite database and am looking for methods to speed that up since doing single inserts for each is pitifully slow.

So far i got this:
############################################################
my $csv_fh = Tie::Handle::CSV->new(
    csv_parser => Text::CSV_XS->new( {allow_whitespace => 1} ),
    file => $unpackedname,
    simple_reads => 1
);

$dbh->do("BEGIN;");

my $sth = $dbh->prepare(
    "REPLACE INTO orders ( orderid, volume, price ) "
    . " VALUES(?,?,?);"
);

while (my $order = <$csv_fh>) {
    $sth->execute(
        $order->{orderid}, $order->{volremain}, $order->{price}
    );
}
close $csv_fh;

$dbh->do("END;");
############################################################

[download]

Now i was hoping to make it a bit faster by using execute_array. The code for that looks as follows and matches the example given in the DBI documentation:
############################################################
my $csv_fh = Tie::Handle::CSV->new(
    csv_parser => Text::CSV_XS->new( {allow_whitespace => 1} ),
    file => $unpackedname,
    simple_reads => 1
);

$dbh->do("BEGIN;");

my $sth = $dbh->prepare(
    "REPLACE INTO orders ( orderid, volume, price ) "
    . " VALUES(?,?,?);"
);

my $insert_count = 0;
while (my $order = <$csv_fh>) {
    $sth->bind_param_array(
        ++$insert_count, 
        [ $order->{orderid}, $order->{volremain}, $order->{price} ]
    );
}
close $csv_fh;

$sth->execute_array( { ArrayTupleStatus => \my @tuple_status } );
    
$dbh->do("END;");
############################################################

[download]

However that creates this error message:
DBD::SQLite::st execute_array failed:
84490 bind values supplied but 3 expected

[download]

Did i do something wrong or is this a limitation of SQLite? Does someone maybe have any suggestions for other ways to speed this up?

In reply to Mass inserts with SQLite + CSV + execute_array? by Xenofur

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.