perlquestion
Anonymous Monk
Hi Monks!<br>
I am having speed issues with this code. I put together this sample code so I can get
some suggestions. The block of commented out code is the explicit way of the code right
after it. I am suspecting that the line <br>
<code>my $control_num = join ( ',', ('?') x @control );</code> <br>
where I am joining all the control numbers from the array "@control" is
slowing down the query results. I wonder if anyone here would have a suggestion on how
I could improve( speed up) this code or query if someone has done something similar.<br>
Thanks for looking!!!
<code>
#!/usr/bin/perl -w
use strict;
use CGI qw(:standard);
$| = 1; # Disable buffering
my $q = new CGI;
my $db = 'TESTDB';
my $dbh = test::test->connect( $db );
print "\nTesting\n\n";
=code
my $sql = $dbh->exec_select("
SELECT acc, name,date
FROM mytable
WHERE mame<>''
AND ((date + 1 YEAR) >= (CURRENT_DATE))
AND ((date + 1 YEAR) < (CURRENT_DATE + 240 DAYS))
AND controlnum IN ('12345','8877788','23334455','223445988','222339000','2222333','223475699','22339668','2223999','009999','11220000','77766','88776'
,'9988888','11223756','22388577','9998888','998888','77665555','9999888','88877777','666555555','88777766666','112223333','9998877','9988877'
,'888777666666','00988777','887777654')
and date between '2010-11-11' and '2011-11-11' order by date desc");
=cut
#=code
my @control = qw(12345 8877788 23334455 223445988 222339000 2222333 223475699 22339668 2223999 009999 11220000 77766 88776
9988888 11223756 22388577 9998888 998888 77665555 9999888 88877777 666555555 88777766666 112223333 9998877 9988877 888777666666 00988777 887777654);
my $control_num = join ( ',', ('?') x @control );
my $sql = $dbh->exec_select( "SELECT acc, name,date
FROM mytable
WHERE mame<>''
AND ((date + 1 YEAR) >= (CURRENT_DATE))
AND ((date + 1 YEAR) < (CURRENT_DATE + 240 DAYS))
AND controlnum IN ($control_num)
and date between '2010-11-11' and '2011-11-11' order by date desc
", @control);
#=cut
if(@$sql)
{
for(my $i = 0; $i < @$sql; $i++)
{
my $acc = $sql->[$i]{'acc'} || '';
my $name = $sql->[$i]{'name'} || '';
my $date = $sql->[$i]{'date'} || '';
print "\n$acc - $name - $date\n";
}
}
</code>
3