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\$
6
  • \$\begingroup\$ $accountKey = @row[0] and similar lines assign a list to a scalar variable. Does this code work? \$\endgroup\$ Jul 4, 2014 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, 2014 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\$ Jul 4, 2014 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\$
    – clt60
    Jul 20, 2014 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, 2014 at 16:02

1 Answer 1

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\$
2
  • \$\begingroup\$ That only works for regexes that represent one fixed string. \$\endgroup\$ Jul 4, 2014 at 8:56
  • \$\begingroup\$ @200_success true, it is an assumption on what OP showed so far. \$\endgroup\$
    – mpapec
    Jul 4, 2014 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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