Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

For a better efficiency and speed question!

by Anonymous Monk
on Oct 04, 2011 at 15:04 UTC ( #929567=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks!
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
my $control_num = join ( ',', ('?') x @control );
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.
Thanks for looking!!!
#!/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','777 +66','88776' ,'9988888','11223756','22388577','9998888','998888','77665555','999988 +8','88877777','666555555','88777766666','112223333','9998877','998887 +7' ,'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 22 +3475699 22339668 2223999 009999 11220000 77766 88776 9988888 11223756 22388577 9998888 998888 77665555 999 +9888 88877777 666555555 88777766666 112223333 9998877 9988877 8887776 +66666 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 + 2 +40 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"; } }

Comment on For a better efficiency and speed question!
Select or Download Code
Re: For a better efficiency and speed question!
by Lotus1 (Chaplain) on Oct 04, 2011 at 15:35 UTC
    I am suspecting that the line my $control_num = join ( ',', ('?') x @control ); where I am joining all the control numbers from the array "@control" is slowing down the query results.

    Why worry about speed when you code doesn't work?

    use strict; use warnings; my @control = qw ( 1 2 3 4 5 6 7 ); my $control_num = join ( ',', ('?') x @control ); print "cn=$control_num<<\n"; __DATA__ cn=?,?,?,?,?,?,?<<
      I am sorry but it does work, the reason why you see "?" when you run your little test code is because the question marks are "place holders" in the SQL query.
Re: For a better efficiency and speed question!
by pvaldes (Chaplain) on Oct 04, 2011 at 15:42 UTC
    This is wrong:  Select name ... WHERE mame<>''
      "...WHERE mame" just typed it wrong, but it is ...WHERE name <> '', name is not equal do empty.

        ... two times, your sql is malformed

        =code my $sql = $dbh->exec_select("SELECT acc,name,date FROM mytable WHERE m +ame<>''

        and same problem after the my $control_num line

Re: For a better efficiency and speed question!
by Anonymous Monk on Oct 05, 2011 at 08:40 UTC

    How many rows does that query operate on? How many are returned? How long does the query alone take on the command-line interface?

    If there are a lot of rows returned, you should look into fetching the rows sequentially (with DBI's fetchrow_hashref) instead of fetching the whole resultset into an arrayref.

    You might find the following presentation of much help: http://www.cpan.org/authors/id/TIMB/DBI_AdvancedTalk_200708.pdf

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://929567]
Approved by herveus
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (4)
As of 2014-09-20 18:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (160 votes), past polls