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

I have this DBI script that queries a MySQL database. I prepare all my queries before I execute them. It takes about 30 min to run this script using DBD::mysql and 20+ hours for DBD::CSV. With DBD::MySQL it seems to crunch about 5000 rows at once then rests for 5 minutes. I'm not sure what it's doing.

I read in the book "Programming the Perl DBI" that I can use the attribute "mysql_store_result" and store it in memory. Any other ideas to speed up this query?

The tables are the following sizes:
sec = 21,000 rows @ 2.3 MB
bt = 1274 rows @ 675 KB
stats = 426 rows @ 95 KB
my $database = "db";
my $db_server = "localhost";
my $user = "user";
my $password = "pass";

my $dbh = DBI->connect("DBI:mysql:$database:$db_server",$user,$passwor
+d);

my $sth_sec = $dbh->prepare("SELECT KEY, TEST, INSTANCE, ID, HID FROM 
+sec")
        or die "Can't prepare SQL statement: $DBI::errstr\n";

my $sth_bt = $dbh->prepare("SELECT NAME, STATE FROM bt WHERE ID =? AND
+ HID=?")
        or die "Can't prepare SQL statement: $DBI::errstr\n";

my $sth_stats = $dbh->prepare("SELECT CCS, kbps, MBytes FROM stats WHE
+RE ID=? AND HID=? & INSTANCE=?")
        or die "Can't prepare SQL statement: $DBI::errstr\n";

$sth_sec->execute()
        or die "Can't execute SQL statement: $DBI::errstr\n";

open(TRAFFICSTATS,'>',"NoThroughput.csv") || die("Cannot Open File");
{
   my $old_sel = select(TRAFFICSTATS);
   $| = 1;  # Auto-flush.
   select($old_sel);
}

print TRAFFICSTATS "KEY,ID,HID,NAME,STATE,CCS,kbps,MBytes\n";

while ( my @row = $sth_sec->fetchrow_array ) {

  my ( $keyId,
       $testCarrier,
       $instanceId,
       $id,
       $hId ) = @row;

   $sth_bt->execute( $id, $hId );

   @row = $sth_bt->fetchrow_array;

   my ( $name, $state ) = @row;

   $sth_stats->execute($id, $hId, $instanceId );

   @row = $sth_stats->fetchrow_array;

   my ( $CCS, $kbps, $mBytes ) = @row;

   print TRAFFICSTATS "$keyId,$id,$hId,$name,$state,$CCS,$kbps,$mBytes
+\n";
}

close TRAFFICSTATS;

[download]

Replies are listed 'Best First'.
Re: Speed up DBD::MySQL Queries
by Skeeve (Parson) on Oct 05, 2005 at 19:31 UTC
    I'm no expert in SQL, but can't you reduce it to 1 SQL statement? 
    SELECT
    sec.KEY,
    sec.ID,
    sec.HID,
    bt.NAME,
    bt.STATE,
    stats.CCS, 
    stats.kbps, 
    stats.MBytes
    FROM sec, bt, stats
    WHERE
        sec.ID = bt.id
    AND
        sec.HID = bt.HID
    AND
        sec.ID = stats.id
    AND
        sec.HID = stats.HID
    AND
        sec.INSTANCE = stats.INSTANCE
        ;

    [download]
    $\=~s;s*.*;q^|D9JYJ^^qq^\//\\\///^;ex;print
[reply]
[d/l]
[select]
Re: Speed up DBD::MySQL Queries
by dragonchild (Archbishop) on Oct 05, 2005 at 19:40 UTC
    Skeeve is absolutely correct. Some further items:
    1. Add indices to the tables. Indexing isn't always easy and you should read MySQL's manual or ask your DBA to help you.
    2. ANALYZE and OPTIMIZE your tables. This will provide anywhere from 0% to 10%.
    3. Improve your DBI usage. Use bind params and fetch() instead of fetchrow_array(). That can give you 0% to 40%, or more.
    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
[reply]
Re: Speed up DBD::MySQL Queries
by InfiniteSilence (Curate) on Oct 05, 2005 at 19:36 UTC
    Looks like Skeeve beat me to it: 
    my $big_sql = $dbh->prepare("SELECT sec.KEY, sec.TEST, sec.INSTANCE, s
+ec.ID, bt.key, bt.name, bt.state, stats.CCS, stats.kbps, stats.MBytes
+ from sec, bt, stats where sec.id = stats.id and stats.id = bt.id and
+ bt.id = ? and bt.hid = ?");

    [download]
    Which is untested but I guess is right for MySql. Also, you might want to make sure you have the ID field indexed on all of those tables as well as that HID.

    Celebrate Intellectual Diversity

[reply]
[d/l]
Re: Speed up DBD::MySQL Queries
by runrig (Abbot) on Oct 05, 2005 at 19:39 UTC
    Is there an index on the key columns? Skeeve is right though, the query will likely go faster if you use a single SQL statement with table joins.
[reply]
Re: Speed up DBD::MySQL Queries
by rnahi (Curate) on Oct 05, 2005 at 20:22 UTC

    Aside from the above answers, that are pinning down the problem, I would recommend reading Speeding up the DBI.

    It will show you how to benchmark and profile your code, in addition to some interesting methods for optimizing DBI usage.

[reply]
Re: Speed up DBD::MySQL Queries
by Roger_B (Scribe) on Oct 05, 2005 at 21:17 UTC
    You've had some good advice, particularly doing the join on the database rather than in Perl. I'd like to comment on the following observation:

    it seems to crunch about 5000 rows at once then rests for 5 minutes. I'm not sure what it's doing.

    This is probably due to buffering; output is accumulating in a buffer at a relatively steady rate until the buffer fills, then you get a whole lot of results as the buffer is flushed. As you're doing the join manually in Perl, this is most likely to be buffering of Perl's output, so I'm guessing you must be writing to a file (Perl 'typically' has line buffering when writing to a terminal and block buffering otherwise).

    HTH

    Roger

[reply]

Back to Seekers of Perl Wisdom