From: Dave Aronson Date: 2012-10-23T03:57:01+09:00 Subject: Re: Looking for suggestions processing and comparing 2 very large files On Mon, Oct 22, 2012 at 2:21 PM, Ruby Student wrote: > Every week I get a large file, over 50 millions records The big question is... are these files SORTED, preferably on some UNIQUE key, or at least some order that will remain the same from week to week? If yes, then you can use the same sort of techniques as in the "diff" utility found on every Unix-derived system and many others. (Windows has something similar but the name escapes me at the moment. IIRC, in an ironic twist, this is one of those cases where the Windows command has a *more* cryptic name than its Unix cognate.) How to make a "diff" type program has been covered in gazillions of blog/magazine articles, textbooks, etc., so I won't go into detail. If you're lucky, you might even be able to just use the ones existing on your system, with some shell scripting for glue. On the other claw, if the records are in random order, then you've got a much more serious problem. In that case, ASSUMING that the keys, and number of updated/duplicated records, are both quite small, off the top of my head I think I'd: - Extract the keys from last week's file - Ditto for this week's - Sort those, assuming the keys are sufficiently smaller that this is reasonable - Diff them. - Extract the actual records from both weeks for any matching keys. - Sort and diff, under the same assumption. Or, if the above data sets are not small enough to make sorting reasonable, but the potential dups might at least fit in RAM: - Extract last week's keys into a Set - Initialize a "Needs Further Inspection" (NFI) Set - Iterate over this week's records: = Try to find the key in last week's Set of keys = If seen, remove from last weeks and add to NFI Set = Else process as an Insertion - Anything left in last week's Set is a Removal - (You can now get rid of last week's Set of keys) - Extract last week's full records matching NFI keys, putting them in a hash keyed by the key - Extract this week's records matching NFI keys, looking them up in the hash - Compare the entire records, processing as either Duplicate or Update as needed -Dave -- Dave Aronson, the T. Rex of Codosaurus LLC, secret-cleared freelance software developer taking contracts in or near NoVa or remote. See information at http://www.Codosaur.us/.