fetching all records from table through perl

Question

I am expecting to fetch all 5,812,750 records from table through Perl. Currently, I am getting an out of memory error. Any other best alternative way to read all the records.

sub sane {
    my $self = shift;

    my $dbh = DBI->connect(
       'dbi:Oracle:usbmfs',
       'US', 'states',
       { AutoCommit => 0, RaiseError => 1 }
    );

    my $sth = $dbh->prepare(qq{
        select cpu_id, system, generation, vendor, item,
               week_first_moved, week_last_moved
          from us_item_tbl
    });

    $sth->execute();

    my $rows = @{ $dbh->selectall_arrayref('
        select upc_id, system, generation, vendor, item,
               week_first_moved, eek_last_moved
          from uk_item_tbl
    ') };

    my %lookup;
    foreach my $row (@$rows) {
        my($cpu, sys, $gen, $vend, $item, $wad, $wlm) = @$rows;
        my $ean = sprintf "%02s%05s%05s", $sys, $vend, $item;
        $cpu = sprintf "%014s", $cpu;
        $lookup{$nae}{$cpu} = [$wad, $wlm];
    }       
}

Try setting `RowCacheSize` in the `DBI->connect` call. See [Identify a memory problem in Perl/DBI code](https://stackoverflow.com/a/5528007/8890345). Also it looks like you're running the same query twice. Why is this a stack snippet? Stack snippets are supposed to be used for JavaScript, HTML, or CSS. Does it still have out of memory error if you set `RowCacheSize` and then use `while (my $row = $sth->fetchrow_arrayref)` instead of `selectall_arrayref`? Lastly, the `$nae` variable wasn't declared anywhere in your code as far as I can tell. — Nathan Mills, Mar 03 '22 at 04:15

Nathan Mills · Answer 1 · 2022-03-03T23:27:58.310

I think using selectall_arrayref is why you get Out of Memory error. Try the following code instead.

I changed DBI->connect() invocation to use RowCacheSize set to a smallish value (20), changed selectall_arrayref to use prepare()/execute() instead, and iterates through both query results only fetching one row at a time. Also, I fixed a couple of typos in your code: $sys was missing the dollar sign, wrote $ean instead of $nae, changed "upc_id" to "cpu_id" in the query, and fixed the spelling of "eek_last_moved".

The // in the while condition is the defined or operator, not the m// regex operator. // evaluates its right argument when the left one is undef. fetchrow_arrayref returns undef when it runs out of rows to return; when this happens, fetchrow_arrayref is called on the second query result and the result is put in $row. If Perl complains about the // (probably because you have a perl earlier than 5.10), try using || instead.

sub sane {
    my $self = shift;

    my $dbh = DBI->connect(
       'dbi:Oracle:usbmfs',
       'US', 'states',
       { AutoCommit => 0, RaiseError => 1, RowCacheSize => 20 }
    );

    my $sth = $dbh->prepare(qq{
        select cpu_id, system, generation, vendor, item,
               week_first_moved, week_last_moved
          from us_item_tbl
    });

    $sth->execute();

    my $sth2 = $dbh->prepare('
        select cpu_id, system, generation, vendor, item,
               week_first_moved, week_last_moved
          from uk_item_tbl
    ');

    $sth2->execute();

    my %lookup;
    while (my $row = $sth->fetchrow_arrayref() // $sth2->fetchrow_arrayref()) {
        my($cpu, $sys, $gen, $vend, $item, $wad, $wlm) = @$row;
        my $ean = sprintf "%02s%05s%05s", $sys, $vend, $item;
        $cpu = sprintf "%014s", $cpu;
        $lookup{$ean}{$cpu} = [$wad, $wlm];
    }
}

fetching all records from table through perl

1 Answers1