correct way to do multi-insert/update transaction

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks

I am not familar with perl. I need to insert (and sometimes update) a lot of records in bulk to mysql. I want to do them in a multi insert transaction block for speed so that the indexes are only updated once after all inserts. I have looked up how to do this in perl and I would just like to check the code I have found actually does what i think it does and will perform the inserts/updates as i hope

Listing D
#!/usr/bin/perl

use DBI;

# create database connection
my $dbh = DBI->connect("DBI:mysql:database=somedb;host=localhost", "us
+er", "pass", {'RaiseError' => 1, 'AutoCommit' => 0});

# trap errors using eval{}

my $insert_sth = $db->prepare("insert into table (field) values(?)");
eval {
    
    #go around some loop to get a value
    for 1...100000 {
        $insert_sth->execute($_);
   }
  
    # commit changes
    $dbh->commit();
};

# any errors
# rollback
if ($@) {
    print "Transaction aborted: $@";
    $dbh->rollback();
}

# close connection
$dbh->disconnect();
[download]

This is a trivial example but i hope it does what i am intending?

Many thanks

Comment on correct way to do multi-insert/update transaction Download Code

Replies are listed 'Best First'.
Re: correct way to do multi-insert/update transaction by kennethk (Abbot) on Apr 28, 2011 at 13:33 UTC
I might do things a little differently, but this will generally do what you need from an algorithmic stand point. There are two syntax errors in your code: In a normal `for` loop, your list has to be delimited by parentheses. See Foreach Loops in perlsyn. You use both `$dbh` and `$db` for your database handle. Without strict, this would have given you a confusing error. See Use strict warnings and diagnostics or die or The strictures, according to Seuss. You might also benefit from a read through Basic debugging checklist. In the end, your code might look something closer to #!/usr/bin/perl use DBI; use strict; use warnings; use diagnostics; # create database connection my $dbh = DBI->connect("DBI:mysql:database=somedb;host=localhost", "user", "pass", { 'RaiseError' => 1, 'AutoCommit' => 0, }, ); # trap errors using eval{} my $insert_sth = $dbh->prepare("insert into table (field) values(?)"); eval { #go around some loop to get a value for (1...100000) { $insert_sth->execute($_); } # commit changes $dbh->commit(); }; # any errors # rollback if ($@) { print "Transaction aborted: $@"; $dbh->rollback(); } # close connection $dbh->disconnect(); [download]	[reply] [d/l] [select]
Re^2: correct way to do multi-insert/update transaction by Anonymous Monk on Apr 28, 2011 at 13:40 UTC
thanks - is there a way to award credit or points for an answer?	[reply]
Re^3: correct way to do multi-insert/update transaction by kennethk (Abbot) on Apr 28, 2011 at 13:47 UTC
You're welcome. There is, but you have to have an account and have hung around the monastery long enough to earn the right to vote. See Voting/Experience System.	[reply]
Re: correct way to do multi-insert/update transaction by moritz (Cardinal) on Apr 28, 2011 at 13:28 UTC
This is a trivial example but i hope it does what i am intending? Just try it. Compare the run time of your script with a version that sets AutoCommit => 1 and never explicitly commits. Perl 6 - second systems done right	[reply]
Re: correct way to do multi-insert/update transaction by JavaFan (Canon) on Apr 28, 2011 at 14:57 UTC
I want to do them in a multi insert transaction block for speed so that the indexes are only updated once after all inserts. Eh, this is the first time I hear that MySQL only updates indices on a commit. `for 1...100000 { $insert_sth->execute($_); }` [download] Forget about indices and transactions. You're problem is doing 100000 queries. 100000 times the latency between your application and the database. Want speed? Write a single statement^* and execute that. ^*If you would insert millions of integers, your query becomes larger than the max package size. Either increase that, or create a handful of statements, each slightly smaller than the max package size.	[reply] [d/l]