speed up slow Perl code with SQL statements

[crmpicco]

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 ways to help speed it up?

I feel the speed is slowing at the @filescursor query.

I'm using use strict;


@groupscursor = $db->query("select distinct(filename),groups.receiverid,groups.filelimit,groups.theowner,groups.active,receivers.name,r eceivers.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|<font color='$colour'>| . $receivername . qq|</font>|,
qq|<font color='$colour'>| . ($filename .".". $extension) . qq|</font>|,
qq|<font color='$colour'>| . $groupsreturned->{filelimit} . qq|</font>|,
qq|<font color='$colour'>| . $groupsreturned->{active} . qq|</font>|,
qq|<font color='$colour'>| . $created . qq|</font>|,
qq|<font color='$colour'>| . $completed . qq|</font>|,
qq|<font color='$colour'>| . $offers . qq|</font>|
]);
$tmpfile = ($filename.".".$extension);
$tmpreceiver = $receivername;
}
}
@filescursor = undef;
}
}
@groupscursor = undef;

[Jeff Mott]

My best guess, actually, would be that ordering (sorting) is the more expensive part of that query. At n log n, it means that every record you have selected will need to be read and compared at least once before the results are returned to you.

[Nedals]

A number of your WHERE's are using stings
eg: where theowner = '$theowner' and filename = '$filename'

String comparisons are time consuming in SQL.
If you have not already done so, you might want to index all the columns that are used in WHERE clauses; especially if you have a reasonably large database.

I had a situation where a database query was taking 20+ seconds. With indexing this same query dropped to less than a second.