Hi,

I have Two Excel workbooks with 4 sheets each. They contain 50000 records for each work book.I use DBI::Excel to fetch details for charts and graphs. But I could not continue, as this script takes almost 2.2 GB of RAM. Am I doing something wrong?

Here is my code.I hope you will understand what I am trying to achieve here.

use strict;
use warnings;
use DBD::Excel;
use DBI;
use Modern::Perl;
use Data::Dumper;
my %clients;
my $preMonthDB = DBI->connect("DBI:Excel:file=March_15.xls")
        or die "Cannot connect: " . $DBI::errstr;
my @preSheets = $preMonthDB->func('list_tables');
foreach my $preSheet (@preSheets)
{
    say "$preSheet";
    my $gender = $preMonthDB->prepare("SELECT sex,count(*) FROM " . $p
+reSheet . " WHERE Scheduled_by <> 'subscriber' GROUP BY sex")
                or die "Couldn't prepare statement: " . $preMonthDB->e
+rrstr;
    $gender->execute()             # Execute the query
            or die "Couldn't execute statement: " . $gender->errstr;

    while (my @genders = $gender->fetchrow_array) {
        $clients{$preSheet}{"previous"}{$genders[0]} = $genders[1];
      }
    
    my $tests = $preMonthDB->prepare("SELECT type_of_test, count(*) FR
+OM " . $preSheet . " WHERE Scheduled_by <> 'subscriber' GROUP BY type
+_of_test")
                or die "Couldn't prepare statement: " . $preMonthDB->e
+rrstr;
    $tests->execute()             # Execute the query
            or die "Couldn't execute statement: " . $tests->errstr;
    my $testRes =  $tests->rows;
    while (my @test = $tests->fetchrow_array) {
       $clients{$preSheet}{"previous"}{"Tests"}{$test[0]} = $test[1];
      }
    my $location = $preMonthDB->prepare("SELECT Location_Name, count(*
+) FROM " . $preSheet . " WHERE Scheduled_by <> 'subscriber' GROUP BY 
+Location_Name")
                or die "Couldn't prepare statement: " . $preMonthDB->e
+rrstr;
    $location->execute()             # Execute the query
            or die "Couldn't execute statement: " . $location->errstr;
    my $locationRes =  $location->rows;
    say "No Of Test Types are " . $locationRes;
    while (my @locations = $location->fetchrow_array) {
        $clients{$preSheet}{"previous"}{"Locations"}{$locations[0]} = 
+$locations[1];
      }
    my $newPatient = $preMonthDB->prepare("SELECT count(*) FROM " . $p
+reSheet . " WHERE Scheduled_by <> 'subscriber' AND New_patient = 'YES
+'")
                or die "Couldn't prepare statement: " . $preMonthDB->e
+rrstr;
    $newPatient->execute()             # Execute the query
            or die "Couldn't execute statement: " . $newPatient->errst
+r;
    my ($newPatientRes) = $newPatient->fetchrow_array;
    $clients{$preSheet}{"previous"}{"new"} = $newPatientRes;
    
    my $totalPatients = $preMonthDB->prepare("SELECT count(*) FROM " .
+ $preSheet . " WHERE Scheduled_by <> 'subscriber'")
                or die "Couldn't prepare statement: " . $preMonthDB->e
+rrstr;
    $totalPatients->execute()             # Execute the query
            or die "Couldn't execute statement: " . $totalPatients->er
+rstr;
    my ($totalPatientsRes) = $totalPatients->fetchrow_array;
    $clients{$preSheet}{"previous"}{"total"} = $totalPatientsRes;
    $clients{$preSheet}{"previous"}{"existing"} = $totalPatientsRes - 
+$newPatientRes;
}
$preMonthDB->disconnect();
my $curMonthDB = DBI->connect("DBI:Excel:file=Appointments_April_15.xl
+s")
        or die "Cannot connect: " . $DBI::errstr;
my @curSheets = $curMonthDB->func('list_tables');
foreach my $curSheet (@curSheets)
{
    my $gender = $curMonthDB->prepare("SELECT sex,count(*) FROM " . $c
+urSheet . " WHERE Scheduled_by <> 'subscriber' GROUP BY sex")
                or die "Couldn't prepare statement: " . $curMonthDB->e
+rrstr;
    $gender->execute()             # Execute the query
            or die "Couldn't execute statement: " . $gender->errstr;

    while (my @genders = $gender->fetchrow_array) {
        $clients{$curSheet}{"current"}{$genders[0]} = $genders[1];
      }
    
    my $tests = $curMonthDB->prepare("SELECT type_of_test, count(*) FR
+OM " . $curSheet . " WHERE Scheduled_by <> 'subscriber' GROUP BY type
+_of_test")
                or die "Couldn't prepare statement: " . $curMonthDB->e
+rrstr;
    $tests->execute()             # Execute the query
            or die "Couldn't execute statement: " . $tests->errstr;
    my $testRes =  $tests->rows;
    while (my @test = $tests->fetchrow_array) {
       $clients{$curSheet}{"current"}{"Tests"}{$test[0]} = $test[1];
      }
    my $location = $curMonthDB->prepare("SELECT Location_Name, count(*
+) FROM " . $curSheet . " WHERE Scheduled_by <> 'subscriber' GROUP BY 
+Location_Name")
                or die "Couldn't prepare statement: " . $curMonthDB->e
+rrstr;
    $location->execute()             # Execute the query
            or die "Couldn't execute statement: " . $location->errstr;
    my $locationRes =  $location->rows;
    say "No Of Test Types are " . $locationRes;
    while (my @locations = $location->fetchrow_array) {
        $clients{$curSheet}{"current"}{"Locations"}{$locations[0]} = $
+locations[1];
      }
    my $newPatient = $curMonthDB->prepare("SELECT count(*) FROM " . $c
+urSheet . " WHERE Scheduled_by <> 'subscriber' AND New_patient = 'YES
+'")
                or die "Couldn't prepare statement: " . $curMonthDB->e
+rrstr;
    $newPatient->execute()             # Execute the query
            or die "Couldn't execute statement: " . $newPatient->errst
+r;
    my ($newPatientRes) = $newPatient->fetchrow_array;
    $clients{$curSheet}{"current"}{"new"} = $newPatientRes;
    
    my $totalPatients = $curMonthDB->prepare("SELECT count(*) FROM " .
+ $curSheet . " WHERE Scheduled_by <> 'subscriber'")
                or die "Couldn't prepare statement: " . $curMonthDB->e
+rrstr;
    $totalPatients->execute()             # Execute the query
            or die "Couldn't execute statement: " . $totalPatients->er
+rstr;
    my ($totalPatientsRes) = $totalPatients->fetchrow_array;
    $clients{$curSheet}{"current"}{"total"} = $totalPatientsRes;
    $clients{$curSheet}{"current"}{"existing"} = $totalPatientsRes - $
+newPatientRes;
}
$curMonthDB->disconnect();
print Dumper(%clients);
[download]

When I use single sheet with 100 rows, it does not take that much RAM. This means that DBI is not using unused rows. Point me in the right direction. Thanks

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.