For some reason this code caught my eye and I started to clean it up. Then I decided to write down notes on the steps I was taking because it provided such a nice little microcosm on Perl refactoring. The sort of code below is very common.
So I've written up a bunch of slides showing the steps I walked through to understand and cleanup the code below. It's rough, but should be readable. Should provide a nice real-world example to go along with the refactoring talk I gave at YAPC::Europe. http://www.pobox.com/~schwern/talks/Refactoring_DBI_Example/ http://www.pobox.com/~schwern/talks/Refactoring/ On Tue, Oct 01, 2002 at 10:14:03AM +0200, Davide Copelli wrote: > 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 ? -- Michael G. Schwern <[EMAIL PROTECTED]> http://www.pobox.com/~schwern/ Perl Quality Assurance <[EMAIL PROTECTED]> Kwalitee Is Job One We have returned to claim the pyramids.
