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

Any help speeding this mySQL query in this perl script
would be great appreciated. 
my $orf_id = $pt->get("$i,2");
$dbh->do(q{ CREATE TEMPORARY TABLE IF NOT EXISTS moo(id INT UNSIGNED, 
+escore DOUBLE, org_id MEDIUMINT)});

$sql1 = q{
  INSERT INTO moo
  #First of two staements to get all hits for orf
  SELECT      id2, escore, org_id2
  FROM        blast, protein
  WHERE       id1 = id
  AND         text_id = ?
};

$sth2 = $dbh->prepare($sql1);
$sth2->execute($orf_id);

$sql1 = q{
  INSERT INTO moo
  # Second of two statements to get all hits for orf
  SELECT      id1, escore, org_id1
  FROM        blast, protein
  WHERE       id2 = id
  AND         text_id = ?
};

$sth2 = $dbh->prepare($sql1);
$sth2->execute($orf_id);

$sql1 = q{
  SELECT DISTINCT min(escore) as escore, org_id
  # Get min(escore) for each org = Main results
  FROM moo
  GROUP BY org_id
};

[download]
As you can see the mySQL query is in a perl script. I need to do to separate queries to get all of the data I need
back and then do this each time for each piece of data in a list. Ouch.
Thanks,
Mark

edited by ybiC: balanced <code> tags

Replies are listed 'Best First'.
Re: speeding up mySQL query in .pm
by perrin (Chancellor) on Aug 14, 2003 at 19:31 UTC
    If your run these multiple times in your program, use prepare_cached instead of prepare.
[reply]
Re: speeding up mySQL query in .pm
by monktim (Friar) on Aug 14, 2003 at 19:15 UTC
    Does mySQL have UNION? If so you can do this: 
    $sql = qq/  SELECT DISTINCT min(escore) as escore, org_id
            FROM blast, protein
            WHERE EXISTS (  SELECT id2, escore, org_id2
                            FROM blast, protein
                            WHERE id1 = id
                            AND text_id = ?
                            UNION
                            SELECT id1, escore, org_id1
                            FROM blast, protein
                            WHERE id2 = id
                            AND text_id = ? )
             GROUP BY org_id
          /;

    [download]
    UNION in T-SQL gets rid of duplicate rows for you. So if UNION works the same way in mySQL then you reduce the overhead of making multiple queries and you won't need a temp table.

    Update: Updated the query to include the DISTINCT and GROUP BY.

[reply]
[d/l]
      Unless mySQL and T-SQL (what's T-SQL?) are very different from Oracle, I think you mean this: 
      SELECT DISTINCT min(tmp.escore) as escore, tmp.org_id
FROM (
    SELECT id2, escore, org_id2
    FROM blast, protein
    WHERE id1 = id
    AND text_id = ?
    UNION
    SELECT id1, escore, org_id1
    FROM blast, protein
    WHERE id2 = id
    AND text_id = ? ) tmp
GROUP BY tmp.org_id

      [download]
      If I knew what columns belonged to which tables I might be able to offer more advice - without that information, I get a headache every time I try to visualize what's going on here...
      Update
      With the WHERE EXISTS, you end up selecting from the original blast and protein tables if the subselect returns any rows, when what you want to do is select from the subselect results.
[reply]
[d/l]
        Good call bean. Your query is more succinct. I cast you a vote :). T-SQL = Transact-SQL = MS flavor of SQL in MS SQL Server.

        Anonymous Monk: is sounds like you need to put a use DBI; at the top of your program. Since you're a self proclaimed novice you make want to check this out Before asking a database related question .... It's a great read.

        Good luck.

[reply]
      Monktim, I'm trying to work your code into mine and I'm having some
      problems any help would be appreciated. Here's what I tried:
      my $orf_id = $pt->get("$i,2");
      #$dbh->do(q{ CREATE TEMPORARY TABLE IF NOT
      EXISTS
      moo(id INT UNSIGNED, escore DOUBLE, org_id
      MEDIUMINT)});


      $sql1 = qq/ SELECT DISTINCT min(escore) as escore,
      org_id
      FROM blast, protein
      WHERE EXISTS ( SELECT id2, escore, org_id2
      FROM blast, protein
      WHERE id1 = id
      AND text_id = ?
      UNION
      SELECT id1, escore, org_id1
      FROM blast, protein
      WHERE id2 = id
      AND text_id = ? )
      GROUP BY org_id
      /;
      $sth2->prepare($sql1);
      $sth2->execute($orf_id);
      with the above I got the following error:

      Background Error: Can't locate object method "prepare" via package "DBI::st" (p rhaps you forgot to load "DBI::st"?) at C:/Perl/site/lib/RG/PropTable.pm line 1 75.
      I've inhereted the code and a novice at best.
      Any ideas on where the problem is?
      Thanks,
      Mark
[reply]
      Yes, MySQL does have UNION, but in versions > 4.x, watch out for that...
[reply]
Re: speeding up mySQL query in .pm
by esh (Pilgrim) on Aug 14, 2003 at 21:19 UTC

    It wasn't clear to me from your description if your problem is that each SQL statement is taking a long time or if you had a lot of distinct SQL statements you needed to write (i.e., the first two being examples of some long list).

    If the problem is writing multiple variations of the same statement a lot of times, you could generate the SQL text more dynamically. For example; 

    for my $n ( 1..200 ) {
  my $sql = qq{ INSERT INTO moo
                SELECT id$n, escore, org_id$n
                FROM blast, protein
                WHERE id$n = id
                AND text_id = ?};
  [...]
}

    [download]

    Note the replacement of q{} with qq{} to do interpolation of $n.

    It is possible that you could do something on the database side to improve the query preformance once you have identified particular statements that are taking a long time.

    If you're not sure what is taking a long time, you might check out DBI::Profile to help identify SQL statements with poor performance.

    -- Eric Hammond

[reply]
[d/l]

Back to Seekers of Perl Wisdom