script causing high cpu usage

Douglas Bagnall douglas at katipo.co.nz
Thu Mar 13 16:49:02 CST 2003


hi Don,

You won't be getting more database connections than you ask for, so 
unless $dbh->connect() is being runs several times, that won't be the 
problem. One database handler is enough.

For the statement handlers, I believe perl's garbage handler will finish 
them up when (or soon after) they are no longer accessible (ie, when 
$sth is redefined for the next statement), but it is of course good 
practice to do it explicitly.

I suspect you will do better by adding indexes to the MySQL table - 
we've had hundredfold improvements for some things.

You'd probably want to look first at the NeTraMet_Daily1 table, because 
it's used in the inner loop. What you want is an index over the columns 
in the WHERE clause.

MySQL's EXPLAIN function is useful for this kind of thing.
see http://mysql.com/doc

Now a slightly irrelevent tip:

Don Jones wrote:
>         print STDERR "select address from addressinfo,personinfo,subnetinfo
> where team = '$Team' and addressinfo.person = personinfo.person and
> organisation = '$CurrentOrganisation' and subnetinfo.subnet =
> addressinfo.subnet\n";
>         $sth = $dbh->prepare("select address from
> addressinfo,personinfo,subnetinfo where team = '$Team' and addressinfo.person
> = personinfo.person and organisation = '$CurrentOrganisation' and
> subnetinfo.subnet = addressinfo.subnet");
>         $sth->execute;

I'd rewrite this as:

   my $query = "SELECT address FROM [etc]";
   warn $query;
   $sth = $dbh->prepare( $query );

so you know the print STDERR / warn is showing exactly what you're 
using. It's otherwise easy to change one and not the other and spend 
hours wondering what's happening.

Also, if you in anyway distrust your variables, you can replace '$Team' 
and '$CurrentOrganisation' with question marks, and put the variables in 
the execute call. Thus, (paraphrasing):

   my $query = "select x from y where team = ? and organisation = ?";
   $sth = $dbh->prepare( $query);
   $sth->execute( $Team, $CurrentOrganisation );

which just does the escaping of quotes for you, lest someone slip 
malicious strings in those variables.

hope this helps,

Douglas




More information about the Wellington-pm mailing list