very sloooooow!

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

I've got this code working, but the return data, which is about 20,000 rows and 96 columns takes over 10 minutes to finish showing up in Mozilla firefox. Does anyone see a way to speed this up? Thanks in advance for any help you can give me. Cliff

sub getQuestionaire {
my($col, $count);
$count=0;
text('Here is the questionaire info:');
print "<table width='340' border='1' cellspacing='1' cellpadding='1'>"
+;
print "<tr>";
foreach $col(@cols){
print "<td><b>$col</b></td>";
}
print "</tr>";
my $table='p_questionnaire';
my $fields=join(",",@cols);
my($sth);
my $sql = qq{SELECT $fields FROM $table ORDER BY patient_id};

$sth = $dbhSt->prepare($sql);
$sth->execute;
my %rec =();

    $sth->bind_columns(map {\$rec{$_}} @cols);
while ($sth->fetchrow_arrayref){
if($count eq 25){
print "<tr>";
foreach $col(@cols){
print "<td><b>$col</b></td>";
}
print "</tr>";
$count=0;
}
print "<tr>";
foreach $col(@cols){
my $val= $rec{$col};
if($val eq ''){
$val='NULL';
}
print "<td>$val</td>";
}
print "</tr>";
$count=$count +1;
}

$sth->fetchrow_arrayref;
$sth->finish;
print "</table>";
}
[download]

Comment on very sloooooow! Download Code

Replies are listed 'Best First'.
Re: very sloooooow! by davido (Cardinal) on Apr 21, 2011 at 17:51 UTC
20000 rows, times 96 columns, times 10 bytes per column (why 10? Just had to plug in a number) means that you're outputting a document of 18 megabytes even before injecting markup tags. Markup tags could easily add 50% more to your output. How about breaking up the response into multiple pages? I doubt anyone is going to want to look at a browser filled with 20,000 lines of text. If you're spitting out the text for the purpose of the user saving a file they can manipulate later, how about pre-zipping it, and sending it as a tab delimited text file? That might reduce its size considerably. I just think that rendering an 18-25 megabyte file on screen in a browser is asking a lot of the browser to handle. I could be wrong. Also, pulling 20k records from a database is going to take some time, and ordering (sorting) them takes some time too. Database interactions might be helped along by configuring your database engine for caching. Throwing memory and disk space at the problem could help from the database side. My mind goes to a book I recently browsed through: Developing Web Applications with Mod_Perl, MySQL, and Memcached. The topic revolved around LAMMP: Linux, Apache, MySQL, Perl... That second 'm' is for Memcache, where you literally throw additional memory and computing power at a problem in a modular way. Update: If you're unable to extract better performance then maybe you have to examine how the data will be used. Is it adequate to generate one report per day, and make it downloadable? I'll be interested in hearing what you find. Dave	[reply]
Re^2: very sloooooow! by believer (Sexton) on Apr 22, 2011 at 09:17 UTC
I agree to davido. Why not create a csv file once (or once a day, or ...) that can be opened in Excel?	[reply]
Re^3: very sloooooow! by cliffrubinmusic (Novice) on Apr 22, 2011 at 13:29 UTC
Thanks folks, good answers from one and all, I've paged it at 1000 rows and it's working fine now. Great stuff. I appreciate you all!	[reply]
Re: very sloooooow! by Old_Gray_Bear (Bishop) on Apr 21, 2011 at 17:11 UTC
Certainly, there are ways to speed this up. But first you need to find out where the bottle-neck is. This process is called 'profiling my code'. The NYTProfiler is your friend. Once you know where your slow-up is, the solution(s) will usually be very apparent. ---- I Go Back to Sleep, Now. OGB	[reply]
Re: very sloooooow! by eff_i_g (Curate) on Apr 21, 2011 at 17:27 UTC
1. Make sure `patient_id` is indexed. 2. Page the output. If you're using MySQL you can do this with `LIMIT`.	[reply] [d/l] [select]
Re: very sloooooow! by SuicideJunkie (Vicar) on Apr 21, 2011 at 17:18 UTC
Use the script with your prints going to a file or console instead of the network. See what difference that makes. Apply a profiler (as in the post above) to the remainder and see what is slow. If it is still taking 10 minutes, a couple of progress print statements may be sufficient Fix whatever code is slow / occurs right after the point it stops making progress.	[reply]