Comparing records in file and reporting stats - Scenario 1

Question

The requirements are :

Fact 1 : We have some data files produced by a legacy system

Fact 2 : We have some data files produced by a new system that should eventually replace the legacy one

Fact 3 :

1. Both the files are text/ASCII files, with records being composed of multiple lines.
2. Each line, within a record, consists of a fieldname and fieldvalue.
3. The format in which the lines are presented are different between 1 and 2, but fieldname and fieldvalue can be extracted from each line through use of regex
4. Field names can change between 1 and 2, but we have a mapping that relates them
5. Each record has a unique identifier that helps us relate the legacy record with a new record as ordering of records in the output file need not be same across both systems.
6. Each file to compare is a minimum of 10 MB to an average case of 30 - 35 MB

Fact 4 : As and when we iterate though building the new system, we would need to compare the files produced by both systems under exact same conditions and reconcile the differences.

Fact 5 : This comparison is being done manually using an expensive visual diff tool. To help in this, I wrote a tool that brings the two different fieldnames into a common name and then sorts the field names in each record, in each file, so that they sync in order (new files can have extra fields that is ignored in the visual diff)

Fact 6 : Due to the comparison being done manually by humans, and human making mistakes, we are getting false posetives AND negatives that is significantly impacting our timelines.

Obviously the question is, what should 'ALG' and 'DS' be?

The scenario I have to address :

Where people continue to inspect the diff visually - in this, the performance of the exsiting script is dismal - most of the processing seems to be in sorting the array of lines in lexicographic order (reading/fetching array element : Tie::File::FETCH, Tie::File::Cache::lookup and putting it in it's correct place so that it's sorted : Tie::File::Cache::insert, Tie::File::Heap::insert)

use strict;
use warnings;

use Tie::File;

use Data::Dumper;

use Digest::MD5 qw(md5_hex);

# open an existing file in read-only mode
use Fcntl 'O_RDONLY';

die "Usage: $0 <unsorted input filename> <sorted output filename>" if ($#ARGV < 1);

our $recordsWrittenCount = 0;
our $fieldsSorted = 0;

our @array;

tie @array, 'Tie::File', $ARGV[0], memory => 50_000_000, mode => O_RDONLY or die "Cannot open $ARGV[0]: $!";

open(OUTFILE, ">" .  $ARGV[1]) or die "Cannot open $ARGV[1]: $!";

our @tempRecordStorage = ();

our $dx = 0;

# Now read in the EL6 file

our $numberOfLines = @array; # accessing @array in a loop might be expensive as it is tied?? 

for($dx = 0; $dx < $numberOfLines; ++$dx)
{
    if($array[$dx] eq 'RECORD')
    {
     ++$recordsWrittenCount;

     my $endOfRecord = $dx;

     until($array[++$endOfRecord] eq '.')
     {
      push @tempRecordStorage, $array[$endOfRecord];
      ++$fieldsSorted;
     }

     print OUTFILE "RECORD\n";

     local $, = "\n";
     print OUTFILE sort @tempRecordStorage;
     @tempRecordStorage = ();

     print OUTFILE "\n.\n"; # PERL does not postfix trailing separator after the last array element, so we need to do this ourselves)

     $dx = $endOfRecord;  
    }
}

close(OUTFILE);

# Display results to user

print "\n[*] Done: " . $fieldsSorted . " fields sorted from " . $recordsWrittenCount . " records written.\n";

So I thought about it and I believe, some sort if a trie, maybe suffix trie/PATRICIA trie, so that on insertion itself the fields in each record are sorted. Hence, I would not have to sort the final array all in one go and the cost would be amortized (a speculation on my part)

Another issue arises in that case - Tie::File uses array to abstract lines in a file - reading lines into a tree and then serializing them back into an array would require additional memory AND processing/

Question is - would that cost more than the current cost of sorting the tied array?

Answer 1

Tie::File is very slow. There are two reasons for this: First, tied variables are significantly slower than standard variables. The other reason is that in the case of Tie::File the data in your array is on disk rather than in memory. This greatly slows access. Tie::File's cache can help performance in some circumstances but not when you just loop over the array one element at a time as you do here. (The cache only helps if you revisit the same index.) The time to use Tie::File is when you have an algorithm that requires having all the data in memory at once but you don't have enough memory to do so. Since you're only processing the file one line at a time using Tie::File is not only pointless, it's harmful.

I don't think a trie is the right choice here. I'd use a plain HoH (hash of hashes) instead. Your files are small enough that you should be able to get everything in memory at once. I recommend parsing each file and building a hash that looks like this:

%data = (
  id1 => {
    field1 => value1,
    field2 => value2,
  },
  id2 => {
    field1 => value1,
    field2 => value2,
  },
);

If you use your mappings to normalize the field names while building the data structure it will make the comparison easier.

To compare the data, do this:

1. Perform a set comparison of the keys of the two hashes. This should generate three lists: The IDs present in just the legacy data, the IDs present in just the new data, and the IDs present in both.
2. Report the lists of IDs that only appear in one data set. These are records that don't have a corresponding record in the other data set.
3. For the IDs in both data sets, compare the data for each ID field by field and report any differences.