Re: Efficiency on Perl Code!

I have rewritten your code in a form that I like. I am not sure that it will meet your needs, but it will show you some techniques that you can use.

As noted by others, I am only using one database handle.

Then I moved your SQL into a data structure and since you seem to only be using 1 data element for each select I gave them all the name data so that I can simplify the read loop. Look for as data in the SQL.

And put the output in to another data structure.

I also use use strict and catch any errors that DBI should happen to through.

To extend this you can add information to the SQL data structure and even put a subroutine in it to handle each request.

use strict;

my $db = "MyServer";
#my $user = MyServerStuff::odbc->{ $db } { user };
my $user = 'bob';
#my $pass = MyServerStuff::odbc->{ $db } { pass };
my $pass = 'bob';


my $dbh = DBI->connect("DBI:ODBC:$db",$user, $pass, {RaiseError => 1})
+;

my @sql = (
[
"g_total",
<<SQL,
select distinct main.type,
       history.date_forwarded as data,
       COUNT(1) as 'PPending'
  from main,history
 where main.type='1'
       and history.date_forwarded
       between dateadd( day, -7, getdate()) and getdate()
 group by history.date_forwarded, main.type
SQL
], [
"c_g_total",
<<SQL,
select distinct main.type,
       history.date_forwarded as data,
       COUNT(1) as 'PPending'
  from main,history
 where main.type!='1'
       and history.date_forwarded
       between dateadd( day, -7, getdate()) and getdate()
 group by history.date_forwarded,main.type
SQL
], [
"total_pending",
<<SQL,
select status_now as data,
       COUNT(1) as 'Got Total'
  from main
 where status_now='submitted' and type='1'
       and submitted_date
       between dateadd( day, -7, getdate()) and getdate()
 group by status_now
SQL
], [
"c_total_pending",
<<SQL,
select status_now, COUNT(1) as 'Got Total'
  from main
 where status_now='submitted'and type!='1'
       and submitted_date
       between dateadd( day, -7, getdate()) and getdate()
 group by status_now
SQL
], [
"comp_status_now",
<<SQL,
select status_now as data,
       COUNT(1) as 'All_Completed'
  from main
 where status_now='completed'and type='1'
       and submitted_date
       between dateadd( day, -7, getdate()) and getdate()
 group by status_now
SQL
], [
"c_comp_status_now",
<<SQL,
select status_now as data,
       COUNT(1) as 'All_Completed'
  from main
 where status_now='completed'and type!='1'
       and submitted_date
       between dateadd( day, -7, getdate()) and getdate()
 group by status_now";
SQL
], [
"delete_check",
<<SQL,
select delete_check as data,
       COUNT(1) as 'Total Deleted'
  from main
 where delete_check='yes'and type='1'
       and submitted_date
       between dateadd( day, -7, getdate()) and getdate()
 group by delete_check";
SQL
], [
"c_delete_check",
<<SQL,
select delete_check as data,
       COUNT(1) as 'Total Deleted'
  from main
 where delete_check='yes'and type!='1'
       and submitted_date
       between dateadd( day, -7, getdate()) and getdate()
 group by delete_check
SQL
], [
"total_pending_status_now",
<<SQL,
select status_now as data,
       COUNT(1) as 'Total Pending'
  from main
 where status_now='forwarded' and type='1'
       and submitted_date
       between dateadd( day, -7, getdate()) and getdate()
 group by status_now
SQL
], [
"c_total_pending_status_now",
<<SQL,
select status_now as data,
       COUNT(1) as 'Total Pending'
  from main
 where status_now='forwarded' and type!='1'
       and submitted_date
       between dateadd( day, -7, getdate()) and getdate()
       group by status_now
SQL
]
);

my %sth;
my $n = 0;

for my $sql (@sql) {
    $sth{$sql[0]} = $dbh->prepare($sql[1]);
}

my %data;

sub cleanup
{
    my $ret = shift;
    $ret =~ s/\s+$//g;
    return $ret;
}

for my $sthk (keys %sth) {
    my $sth = $sth{$sthk};

    eval {
        $sth->execute();
    };
    if ($@) {
        die $sth->errstr;
    }
    while (my $pointer = $sth->fetchrow_hashref) {
        my $data = cleanup($pointer->{'DATA'});
        push(@{$data{$sthk}}, $data);
    }
    $sth->finish;
}
[download]

If you need to access the code again you do not need to prepare the data again. You can just run the second for loop.

-- gam3
A picture is worth a thousand words, but takes 200K.

Comment on Re: Efficiency on Perl Code! Download Code


good chemistry is complicated, and a little bit messy -LW
	PerlMonks