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.

Answers


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) { 
  ...;
}

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);   

Need Your Help

Improving Drawing Performance (for Dudel application)

ios performance drawing quartz-graphics drawrect

I needed to create a small drawing/paint application, so i turned to Beginning IPad Development for IPhone Developers: Mastering the IPad SDK By Jack Nutting, Dave Wooldridge, David Mark.

lombok 1.16.4 with maven (jdk7) not working

java maven lombok

I am using the @Data annotation, but compiler doesn't see the get/set methods.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.