Thanks THRAK. Sorry about the lack of code example. I'm doing what you suspect -> looping over a fetch from the statement handle and putting the data in a hash array. I had a feeling I might have to break this up into smaller queries.

Big queries are fine.
However, if you're doing this, check out perltie.
This should cure many evils associated with hash sizes taking you out of memory.

Malk.

It's probably not a problem with your Perl installation. I'd wager that what's going on is that you're pulling all of the data into a big Perl data structure, like an array of array references. That would be the case if you're using fetchall_arrayref to get your data. But this cannot be determined unless you post your code.

Going with my guess: the problem is probably that Perl takes relatively more memory to store a given amount of data than SQL*Plus, but that's in the nature of the Perl beast => throw memory at a problem to increase efficiency.

A possble solution is to change your code to work with fetchrow_arrayref (note: "row", not "all") which gets the data from the result set one row at a time, instead of snarfing the whole thing into memory at once. That may reduce the program's memory needs. It might slow down your program's execution -- but having it finish in 10 minutes is faster than never, I suppose =)

Philosophy can be made out of anything. Or less -- Jerry A. Fodor

Here is my code:

        $sth = $dbh->prepare($select);
    $sth->execute();
    while(@data = $sth->fetchrow_array())
    {
            $curpidm = $data[0];
            $curcrn = $data[8];
            unless($seen_pidm{$curpidm})
            {
                $seen_pidm{$curpidm} = 1;
                push(@pidms, $curpidm);
              $main_data{$curpidm}{"Resd"} = $data[1];
              $main_data{$curpidm}{"Edlv"} = $data[2];
              $main_data{$curpidm}{"Styp"} = $data[3];
              $main_data{$curpidm}{"Degc"} = $data[4];
              $main_data{$curpidm}{"Majr"} = $data[5];
              $main_data{$curpidm}{"Egol"} = $data[6];
              $main_data{$curpidm}{"Aprv"} = $data[7];
              $main_data{$curpidm}{"TermCodeEff"} = $data[14];
              $main_data{$curpidm}{"Cipc"} = $data[15];
            }

            $main_data{$curpidm}{$curcrn}{"Subj"} = $data[9];
            $main_data{$curpidm}{$curcrn}{"Crs"} = $data[10];
            $main_data{$curpidm}{$curcrn}{"Section"} = $data[11];
            $main_data{$curpidm}{$curcrn}{"Sess"} = $data[12];
            $main_data{$curpidm}{$curcrn}{"CreditHr"} = $data[13];

        $main_data{$curpidm}{"CurHrs"} += $data[13];

            unless($seen_crn{$curcrn})
            {
                $seen_crn{$curcrn} = 1;
                push(@crns, $curcrn);
            }
    }

    $sth->finish();
[download]

Untested, but let's go for ease of programming here. The suggestion to use MLDBM is very good, as the error is probably due to storing so much in %main_data.

while(@data = $sth->fetchrow_array()) {
    ($curpidm, $curcrn) = @data[0, 8];

    my $dest = $main_data{$curpidm};

    unless($seen_pidm{$curpidm}) {
        $seen_pidm{$curpidm} = 1;
        push(@pidms, $curpidm);

        @$data{qw( Resd Edlv Styp Degc Majr Egol Aprv TermCodeEff Cipc
+ )} =
            @data[1 .. 7, 14, 15];
    }

    my $crn = $dest->{$curcrn};
    @$crn{ qw( Subj Crs Section Sess CreditHr )} = @data[9 .. 13];

    $dest->{CurHrs} += $data[13];

    unless($seen_crn{$curcrn}) {
        $seen_crn{$curcrn} = 1;
        push(@crns, $curcrn);
    }
}

# probably unnecessary
# $sth->finish();
[download]

If that's not correct syntax, it's pretty close. At least it's shorter.

The villain here is probably the %main_data hash, which is filling up with all your fetched data. So it's not when you get it from the DB, it's when you squirrel it away for use in your program.

The minimal mutilation solution is to do what OS's do to extend their memory: use the hard drive. You can use the MLDBM module which allows you to store complex Perl data structures on disk and manipulate them transparently (when you tie the data structure).

Not completely related to that suggestion: something that might help you make this stretch of code more maintainable: use a hash slice and an array slice instead of those multiple assignments:

@fields = qw(Resd Edlv); # just a sample
@main_data{curpidm}{@fields} = @data[1,2];
[download]

Update gotta check that, not sure it will work as advertised ...

Second update it won't ... the handy @hashname shorthand is short-circuited by the fact that we've got a reference here. So, unless somebody else figures out the syntax, disregard this suggestion.

HTH

Philosophy can be made out of anything. Or less -- Jerry A. Fodor