CODEWITHSUNDEEP
perl
user2223335
user2223335

Reputation: 209

Bulk File Processing in Perl

I have a perl program which is taking huge time.Can someone suggest the option for tuning.
Requirement
Perl program doing some file processing after database retrieval and further processing required based on values present in database. So the logic is 

my $sql="select KEY,VALUE from TABLEA";    
 my $sth = $dbh->prepare($sql);
    $sth->execute;
while ( my @row = $sth->fetchrow_array( ) ) {
        $tagdata{@row[0]} = @row[1];
}

TABLEA contains 3 million rows. Now in perl program after so many file processing, I need to find the key for a given value. The key is unique but the value is not.
So key is found out by following logic. 

my @keysfind = grep { $tagdata{$_} eq $value } keys %tagdata;
            foreach (@keysfind)
            {

and based on@keysfind the processing is done. This process is taking huge time as this(finding the key) is run in a loop (0.1 million times).
The options which i tried are
1) Use fetchall_hashref instead of fetchrow_array. Though it was little faster but not much.
2) Instead of having hash, move all these operations into database i.e. fetch the key based on value but the problem is that this value fetching loop runs 0.1 million times which means it will have these number of database calls though the query will be straightforward.

Can anyone suggest a better approach to handle this.

Upvotes: 0

Views: 112

Answers (2)

amon
amon

Reputation: 57600

The probably best solution is to delegate finding the keys to the database, as shown in chorobas answer.

For academic purposes only, here is a way to find matching keys in constant time without using the database. All we need is a reverse hash that maps values to an array of keys:

my %tagdata;
my %reverse_tagdata;
my $sth = $dbh->prepare('select KEY,VALUE from TABLEA');
$sth->execute;
while ( my ($key, $value) = $sth->fetchrow_array ) {
    $tagdata{$key} = $value;
    push @{ $reverse_tagdata{$value} }, $key; # add key to matching values
}

...;

my $value = ...;
my @found_keys = @{ $reverse_tagdata{$value} }; # one simple hash lookup
for my $key (@found_keys) { 
  ...;
}

Upvotes: 1

choroba
choroba

Reputation: 241838

If you can, let the database do the hard work:

my $sql = 'select KEY, VALUE from TABLEA where VALUE = ?';    
my $sth = $dbh->prepare($sql);
$sth->execute($value);

Upvotes: 4

Related Questions