speed up slow Perl code with SQL statements

crmpicco · March 30th, 2007, 06:27 AM

This is the guts of the code that I have written, however, when displayed in Firefox it takes a while to display. This seems to be down to the SQL hits i think, is there a way to optimize it as best as possible? I tried a JOIN which works, but is there any other way?

I'm using

Code:

use strict;

Code:

@groupscursor = $db->query("select distinct(filename),groups.receiverid,groups.filelimit,groups.theowner,groups.active,receivers.name,receivers.extension from hols.groups left join hols.receivers on groups.receiverid = receivers.id order by theowner asc, receiverid asc");

if ($groupscursor[1] ne "0E0") {
    while ($groupsreturned = $groupscursor[0]->fetchrow_hashref) {
        $receiverid        = $groupsreturned->{receiverid};
        $filename        = $groupsreturned->{filename};
        $theowner        = $groupsreturned->{theowner};
        $receivername    = $groupsreturned->{name};
        $extension        = $groupsreturned->{extension};    

        next if ($receivername eq $tmpreceiver and ($filename.".".$extension) eq $tmpfile);


        @filescursor = $db->query("select count(id) as results,id,filename,completed,max(created) as datecreated from picco.files where theowner = '$theowner' and filename = '$filename' group by filename");        

        if ($filescursor[1] ne "0E0") {
            while ($filesreturned = $filescursor[0]->fetchrow_hashref) {

                $fileid        = $filesreturned->{id};
                $created    = $filesreturned->{datecreated};
                $completed    = $filesreturned->{completed};

                $total += $filesreturned->{results};

                ($offers) = @{($db->query("SELECT count(id) as offers FROM picco.offers WHERE fileid = '$fileid'"))[0]->fetchrow_arrayref};

                if ($theowner ne $tmpsite) {            
                    push (@list, ["", qq|<b>| . $theowner . qq|</b>|, "&nbsp;", "&nbsp;", "&nbsp;", "&nbsp;", "&nbsp;", "&nbsp;", "&nbsp;"]);
                    $total += 1;
                }
                $tmpsite = $theowner;

                $colour = "black";

                if ($completed eq "N") {
                    $colour = "red";
                }                

                push (@list,    [
                                    "&nbsp;",                
                                    "&nbsp;", 
                                    qq|| . $receivername . qq||, 
                                    qq|| . ($filename .".". $extension) . qq||, 
                                    qq|| . $groupsreturned->{filelimit} . qq||, 
                                    qq|| . $groupsreturned->{active} . qq||, 
                                    qq|| . $created . qq||,
                                    qq|| . $completed . qq||,
                                    qq|| . $offers . qq||
                                ]);
                $tmpfile = ($filename.".".$extension);
                $tmpreceiver = $receivername;
            }
        }
        @filescursor = undef;
    }
}
@groupscursor = undef;

www.crmpicco.co.uk
www.ie7.com

ciderpunx · April 3rd, 2007, 05:52 AM

I'd put the picco.tables and hols.tables into the same database.
Then you'll be able to get rid of all those nested loops and do a
four way join between picco.files, picco.offers, hols.groups and
hols.receivers.

Something like:

Code:

  SELECT count(offers.id) AS offers, ...etc
    FROM groups, 
         receivers, 
         offers,
         files
   WHERE groups.receiverid = receivers.id
     AND offers.fileid     = files.id
     AND files.filename    = groups.filename ...etc
ORDER BY groups.theowner, groups.receiverid
GROUP BY files.filename

--
Charlie Harvey's website - linux, perl, java, anarchism and punk rock: http://charlieharvey.org.uk

robprell · May 9th, 2007, 08:00 PM

Nice clean sql code (coming from a DBA).

Liked your site, particularly the windows stuff....

Pics of your g-friend says she is way too hot for you!!!

J/K on the last one......

ciderpunx · May 11th, 2007, 05:21 AM

*hehe* Thanks robprell ;)

--
Charlie Harvey's website - linux, perl, java, anarchism and punk rock: http://charlieharvey.org.uk