in reply to How to Speed up MySQL w/ Perl

This is untested, but there are comments you may find helpful in the code. 

#!/usr/bin/perl

use warnings;
use strict;
use DBI;

my $dbh;

#A lexical scope so that the db... variables go out of scope quickly.
{
        my $dbname = "database";
        my $dbuser = "user";
        my $dbpass = "password";
        my $dbhost = "localhost";
        my $dbport = 3306;
        my $dsn    = "DBI:mysql:database=$dbname;host=$dbhost;port=$db
+port";
        $dbh = DBI->connect($dsn, $dbuser, $dbpass, {  RaiseError => 1
+ })
                or die "Couldn't connect to the database: $!\n";
}


#Use placeholders here. Your DBD driver will then take care of
#appropriate quoting.
my $sth = $dbh->prepare("SELECT ID, Data FROM table_name WHERE Data=?"
+);
$sth->execute("value");


#You're executing the update query lots of times.
#Prepare it once, then execute it with different values.

my $update_sth = $dbh->prepare("UPDATE new_table SET newData=? WHERE n
+ewID=?");

#No point doing the looping over your DataHash twice, you've got the d
+ata
#once, why not use it here?
while(my ($id, $data) = $sth->fetchrow) {
        $update_sth->execute($data, $id);
}

#I never bother with these if it's at the end of a script personally.
#They'll happen automatically when the variables get DESTROY'ed
$sth->finish;
$update_sth->finish;
$dbh->disconnect;

[download]

davis
It's not easy to juggle a pregnant wife and a troubled child, but somehow I managed to fit in eight hours of TV a day.

Replies are listed 'Best First'.
Re: Re: How to Speed up MySQL w/ Perl
by tachyon (Chancellor) on May 26, 2004 at 09:34 UTC

    # I never bother with these if it's at the end of a script personally. They'll happen automatically when the variables get DESTROY'ed

    Have you ever actually tested those assumptions? Certainly on my system which uses RH 7.3 Perl 5.6.2, and the latest DBI and DBD::msyql with MySQL 4.0.xx if you fail to disconnect your connections will hang around until they either time out (8 hours default) or you run out of connections.

    cheers

    tachyon

[reply]
      Have you ever actually tested those assumptions?
      No, I thought I'd read it. Good catch tachyon, cheers!
      Sound of davis scrabbling furiously after some production code waving his hands...

      Update: Ah, the bit I've read was about Apache::DBI overloading the disconnect methods. Either way, I was wrong to say what I said, and the finish/disconnect calls ought to be included

      davis
      It's not easy to juggle a pregnant wife and a troubled child, but somehow I managed to fit in eight hours of TV a day.
[reply]
        the finish/disconnect calls ought to be included
        Half right. Finish is not the same as disconnect. You remain connected to your backend until you explicitly disconnect (barring network problems, mod_perl, etc) but finish() happens on its own when you retrieve all the data. Read the DBI docs, you only need to call finish() when you execute a query, then disconnect or DESTROY before fully fetching all rows. In the OP's case, he/she does fetch all rows so you were right the first time about finish, there's no reason to call it at the end of this script.
[reply]
        "InactiveDestroy" (boolean) The "InactiveDestroy" attribute can be used to disable the database engine related effect of DESTROYing a handle (which would normally close a pre-pared statement or disconnect from the database etc). The default value, false, means a handle will be fully destroyed when it passes out of scope.

        It says the above in the DBI docs. If DBD::mysql isn't disconnecting connections when the database handle goes out of scope it is probably a bug.
[reply]
      Ok them heres a question for you! Why bloody not? Unless I'm missunderstanding something, nobody can use these connections, right? So their hanging around has absolutely no purpose. Is there any point in not having them disconnect at the END?
[reply]
Re: Re: How to Speed up MySQL w/ Perl
by rsiedl (Friar) on May 26, 2004 at 08:48 UTC
    Thanks davis.

    That clears up a bit of my previous confusion as well.
    I'll give it a go.

    Cheers,
    Reagen
[reply]

In Section Seekers of Perl Wisdom