Query tuning

Transact-SQL
1

I have tried the below query and it is consuming more time and Out of memory issues.
Out of memory!
can you please let me know to avoid the out of memory issues.

 my $dbh = DBI->connect ('dbi:Oracle:usbmfs', 'US', 'states', {AutoCommit => 0, RaiseError => 1});
  my $sth = $dbh->prepare (qq{ select cpu_id, system, gen, vendor, item, week_first_moved, week_last_moved from us_item_tbl });
        $sth->execute;
        $sth->bind_columns (\(my( $cpu, $sys, $gen, $vend, $item, $wad, $wlm)));
        while ($sth->fetch) {
            my $ean = sprintf "%02s%05s%05s", $sys, $vend, $item;
            $cpu = sprintf "%014s", $cpu;
            $lookup{$nae}{$cpu} = [$wad, $wlm];
        }
    $dbh->disconnect;
    $$self{'SANE'} = \%lookup;
}

output : while executing getting memory issues.

2

This is returning all records from the us_item_tbl table. We don't know how big this is or what it even looks like. If this is a large table, I can see how it would eat up memory coming back. Is there some filter criteria that is missing? Also, this is a SQL Server forum not Oracle

3

and a while loop will definitely kill performance.
try to do it in batch, in one pass

4

@yosiasz can you give an example how to do in batch.

5

what programming language are you using in the process above?

6

oracle and perl

7

I have tried to use fetchall_arrayref function and currently getting out of memory issue. any idea how to clear the cache to get rid of the out of the memory issue.


sub sane {
    my $self = shift;
    my %lookup;
    my $dbh = DBI->connect ('dbi:Oracle:usbmfs', 'US', 'states', {AutoCommit => 0, RaiseError => 1});
    my $sth = $dbh->prepare (qq{ select cpu_id, system, gen, vendor, item,
                                        week_first_moved, week_last_moved
                                     from us_item_tbl });

        $sth->execute ();
        $sth->bind_columns (\(my( $cpu, $sys, $gen, $vend, $item, $wad, $wlm)));
         my $rows = $sth->fetchall_arrayref({});;
         for my $row (@$rows) {
            my $nae = sprintf "%02s%05s%05s", $sys, $vend, $item;
            print "$nae\n";
            $upc = sprintf "%014s", $cpu;
            $lookup{$ean}{$cpu} = [$wad, $wlm];
        }
#    }
    $dbh->disconnect;
    $$self{'SANE'} = \%lookup;
}
8

While, for loop, for each all those are not perfomant.

What is thw code supposed to do at the end?