This looks like a problem more for the beginners list than the fwp list.
With that said, you can create several different statement handles outside
of the while loop and use placeholders (one of the things I LOVE about DBI
and Perl).
$sthOne = $dbh->prepare( "SELECT COUNT(*) FROM lognew WHERE pageid=? AND
data >= ?" );
$sthTwo = $dbh->prepare( "SELECT COUNT(*) FROM lognew WHERE (pageid=? AND
data >= ?)" );
....
while ( $riga = <INPUT> ) {
chomp $riga; # use chomp instead ... it's safer
$sthOne->execute( $riga, $startdate );
$sthTwo->execute( $riga, $dataoggi );
}
There's also a prepare_cached method you can use, too, but I'll defer to
someone with more experience to address its benefits & any disadvantages.
Jason
----- Original Message -----
From: "Davide Copelli" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 01, 2002 4:14 AM
Subject: Mysql performace 30000 record
> I need to execute multiply query on a Mysql database with 30000 record
>
> I use this code to read a txt file with 50 records (eg. page1 , page 2,
page
> 3 etc) and then execute some query
> (the code is ok):
>
> .....
> .....
>
> open (INPUT, "< $filepageid") || &file_open_error("$filepageid");
>
> while ($riga=<INPUT>){
> $nump++;
> chop($riga);
> $pagina[$nump] = $riga;
>
> $sth= $dbh->prepare("SELECT count(*) FROM lognew WHERE
> pageid='$pagina[$nump]' and data>='$startdate'");
> $sth->execute;
> $totalvisit[$nump] = $sth->fetchrow_array();
>
> $sth = $dbh->prepare("SELECT count(*) FROM lognew WHERE
> (pageid='$pagina[$nump]' and data='$dataoggi')");
> $sth->execute;
> $totalvisittoday[$nump] = $sth->fetchrow_array();
>
> $sth = $dbh->prepare("SELECT count(*) FROM lognew WHERE
> (pageid='$pagina[$nump]' and data='$dataieri')");
> $sth->execute;
> $totalyvisit[$nump] = $sth->fetchrow_array();
>
> $sth= $dbh->prepare("SELECT count(*) FROM lognew WHERE
> (pageid='$pagina[$nump]' and data<='$fine30gg' and data>='$inizio30gg')");
> $sth->execute;
> $totalmvisit[$nump] = $sth->fetchrow_array();
>
> }
>
> close (INPUT);
>
> The question is:
>
> Using the same statement object $sth can affect MySql performance ?
> Are there altenative ways to optimize this routine ?
> Are 30000 records to much for this kind of query ?
>
> Thank you very much !
>
>
>
>
>
