6
\$\begingroup\$

I have a script that is standardizing a large amount of data in the database. The standardization involves applying over 500 regular expressions to the data.

Here is some quick pseudocode:

Load the records from the database
for each record
   for each regular expression ##predefined list of 500 regular expressions 
      apply regular expression to record
    end;
    write record to file
end;

Here is the relevant block of code in Perl:

while(my @row = $queryHandle->fetchrow_array())
{
    $accountKey = @row[0];
    $addressLine1 = @row[1];
    $addressLine2 = @row[2];

    #first remove all special characters leaving only numbers and alphabets
    $addressLine1 =~ s/[^A-Za-z0-9 ]//g;
    $addressLine2 =~ s/[^A-Za-z0-9 ]//g;

    for my $regexRef (@regexesList)
    {
        #now standardize the addresses
        $addressLine1 =~ s/$regexRef->{pattern}/$regexRef->{output}/ig;
        $addressLine2 =~ s/$regexRef->{pattern}/$regexRef->{output}/ig;
    }

    my $standardizedAddress = $addressLine1 . $addressLine2;
    $standardizedAddress =~ s/\s+//g; #remove all white space
    #Et_Run_Log("the normalized address is: $normalizedAddress");

    print $dataFileHandle "${standardizedAddress}\n";
    $rowCount++;
}

An excerpt of regexList:

my %regexHash;
$regexHash{pattern} = "\bstr\b";
$regexHash{output} = "street";

#add regex hash to list 
push(@regexesList, {%regexHash});

$regexHash{pattern} = "\brd\b";
$regexHash{output} = "road";
push(@regexesList, {%regexHash});

This is working code but the performance is abysmal. Currently the script has been running for 2.5hours and has written out 3.13 million records to the output file with about 7million to go.

Is this the best it can get? Is there another faster, or less slower way? Maybe writing each row to a file first and then run each regular expression on the whole file?

I would like to know if there is better way to implement this before I try the above mentioned alternative.

\$\endgroup\$
  • \$\begingroup\$ $accountKey = @row[0] and similar lines assign a list to a scalar variable. Does this code work? \$\endgroup\$ – 200_success Jul 4 '14 at 1:45
  • \$\begingroup\$ the code is working code.. its actually "crawling" in test environment right now. Also added an excerpt of the list of hashes @200_success \$\endgroup\$ – Mos No Jul 4 '14 at 1:56
  • 1
    \$\begingroup\$ @200_success It works, but it's not really what OP means - it emits warning with use warnings;: Scalar value @row[0] better written as $row[0]. \$\endgroup\$ – Xaerxess Jul 4 '14 at 8:24
  • \$\begingroup\$ 2.5 hour = 9000 seconds. 3.13 mil records in 9000 seconds: 347 records per second. If for each record is applied 500 regexes this mean 173_000 regexes per second. IMHO, not bad for an interpreted language... \$\endgroup\$ – jm666 Jul 20 '14 at 13:04
  • \$\begingroup\$ @jm666 if you think that is not bad then you will be blown by the final performance achieved thanks to the help of the community at stackexchange and stackoverflow: 15 minutes to process all 10 million records!!! perl is a monster in text processing! \$\endgroup\$ – Mos No Jul 21 '14 at 16:02
2
\$\begingroup\$

Nested loops can be problematic, but you can build regex which can be applied once instead of running foreach

my %replace = (
  str => { re => '\bstr\b', output => "street" },
  rd  => { re => '\brd\b', output => "road" },
);
my ($reCombined) = map qr/($_)/, join "|", map $_->{re}, values %replace;

and later inside while loop,

# my $string = "Foo str bar rd foo";
$string =~ s/$reCombined/$replace{$1}{output}/g;
\$\endgroup\$
  • \$\begingroup\$ That only works for regexes that represent one fixed string. \$\endgroup\$ – 200_success Jul 4 '14 at 8:56
  • \$\begingroup\$ @200_success true, it is an assumption on what OP showed so far. \$\endgroup\$ – mpapec Jul 4 '14 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.