Help with SELECT statement 1

[skosterow]

I am trying to pull data out in a format. Well, heres my code:

my $dsn01 = "DBI:mysqlPP:$DB:ATABASE:$DB::SERVER";
my $dbh01 = DBI->connect($dsn01, $DB::REMOTE, $DB:ASS);
my $sth01 = $dbh01->prepare(qq{ SELECT id, old_id, firstname, mi, lastname, title FROM utl_rads ORDER BY old_id });
$sth01->execute();
while (($EXAM::ID, $EXAM::OLDID, $EXAMS::FIRSTNAME, $EXAMS::MI, $EXAMS::LASTNAME, $EXAMS::TITLE) = $sth01->fetchrow_array())
{
my $dsn02 = "DBI:mysqlPP:$DB:ATABASE:$DB::SERVER";
my $dbh02 = DBI->connect($dsn02, $DB::REMOTE, $DB:ASS);
my $sth02 = $dbh02->prepare(qq{ SELECT id, discription FROM sys_birads ORDER BY discription });
$sth02->execute();
while (($BIRAD::ID, $BIRAD:ES) = $sth02->fetchrow_array())
{
my $dsn03 = "DBI:mysqlPP:$DB:ATABASE:$DB::SERVER";
my $dbh03 = DBI->connect($dsn03, $DB::REMOTE, $DB:ASS);
my $sth03 = $dbh03->prepare(qq{ SELECT id, discription FROM sys_followup ORDER BY discription });
$sth03->execute();
while (($FOLLOWUP::ID, $FOLLOWUP:ES) = $sth03->fetchrow_array())
{
my $dsn04 = "DBI:mysqlPP:$DB:ATABASE:$DB::SERVER";
my $dbh04 = DBI->connect($dsn04, $DB::REMOTE, $DB:ASS);
my $sth04 = $dbh04->prepare(qq{ SELECT COUNT(*) FROM utl_exams WHERE rad = '$EXAM::OLDID' AND birad = '$BIRAD::ID' AND follow_up = '$FOLLOWUP::ID' AND exam_code != 'AR' AND exam_code != 'AV' AND exam_code != 'AB' AND exam_code != 'PM' });
$sth04->execute();
($RECORD::TOTAL01) = $sth04->fetchrow_array();
$sth04->finish();
$dbh04->disconnect();

&RECCOUNT;
}
$sth03->finish();
$dbh03->disconnect();
}
$sth02->finish();
$dbh02->disconnect();

if ($TOTAL::RECORDS ne '0') { $TABLE:ATA .= &quot;<tr><td><br></td><td></td><td></td><td></td><td></td></tr>&quot;; }
}
$sth01->finish();
$dbh01->disconnect();

The problem is that when i execute this it is s-l-o-w. Anything i can to to improve this?

Thanks for any suggestions!

- Scott

 

[miahmiah900]

how large is your data set? are you using primary keys and indexes, if so which columns are they created for? is your web server slow?

 

[hvass]

You will have more success from the MySQL forum if you present your question without th e PHP or Perl whatever it is

You seem to have nested while loops and in each one you are connecting to the database, why? Asking for a database connection requires asking your DNS server to resolve names, then asking the server for a socket and a connection, the server starting a new process under your mysqld ..... and on ....

In theory your nested where loops should be able to be re-written as a block of SQL.

For each person in
utl_rads
you seem to be getting each record in
sys_birads
then you seem to be getting each record in
sys_followup
so that you can finally query
utl_exams

So say each table had only 100 records you seem to be doing 100x100x100 connections and separate queries - yup that will take some time and simply indexing the tables will not help that much.

Try joining the tables and doing a single query

http://www.mysql.com/doc/en/Data_Manipulation.html

then run it as a SQL query direct onto the database without PHP or a web server using the client mysql to see how long it takes. Then post it as a SQL performance question and we may be able to help.

good luch

 

[skosterow]

thanks for the tip!

it works great now: heres my NEW code!

my $dsn02 = &quot;DBI:mysqlPP:$DB:ATABASE:$DB::SERVER&quot;;
my $dbh02 = DBI->connect($dsn02, $DB::REMOTE, $DB:ASS);
my $sth02 = $dbh02->prepare(qq{ SELECT utl_exams.patient, utl_exams.id, utl_exams.exam_id, utl_exams.jacket, utl_exams.service_date, sys_birads.id, sys_birads.discription, sys_followup.id, sys_followup.discription FROM utl_exams, sys_birads, sys_followup WHERE utl_exams.birad = sys_birads.id AND utl_exams.rad = '$SELECT::RAD' AND utl_exams.follow_up = sys_followup.id AND utl_exams.exam_code != 'AR' AND utl_exams.exam_code != 'AV' AND utl_exams.exam_code != 'AB' AND utl_exams.exam_code != 'PM' ORDER BY sys_birads.id, sys_followup.id, utl_exams.service_date; });
$sth02->execute();
while (($PA::ID, $EXAM::ID, $EXAM::OLDID, $EXAM::JACKET, $EXAM::SRVCDATE, $BIRAD::ID, $BIRAD:ES, $FOLLOWUP::ID, $FOLLOWUP:ES) = $sth02->fetchrow_array())
{
# Do Calcs
@split_date = split(/-/,$EXAM::SRVCDATE);
$EXAM::SRVCDATE = &quot;@split_date[1]/@split_date[2]/@split_date[0]&quot;;

&RECCOUNT;

}
$sth02->finish();
$dbh02->disconnect();