Looking up a "key" in an 8GB+ text file

Question

I have some 'small' text files that contain about 500000 entries/rows. Each row has also a 'key' column. I need to find this keys in a big file (8GB, at least 219 million entries). When found, I need to append the 'Value' from the big file into the small file, at the end of the row as a new column.

The big file that is like this:

KEY                 VALUE
"WP_000000298.1"    "abc"
"WP_000000304.1"    "xyz"
"WP_000000307.1"    "random"
"WP_000000307.1"    "text"
"WP_000000308.1"    "stuff"
"WP_000000400.1"    "stuffy"

Simply put, I need to look up 'key' in the big file.

Obviously I need to load the whole table in RAM (but this is not a problem I have 32GB available). The big file seems to be already sorted. I have to check this.
The problem is that I cannot do a fast lookup using something like TDictionary because as you can see, the key is not unique.

Note: This is probably a one-time computation. I will use the program once, then throw it away. So, it doesn't have to be the BEST algorithm (difficult to implement). It just need to finish in decent time (like 1-2 days). PS: I prefer doing this without DB.

I was thinking at this possible solution: TList.BinarySearch. But it seems that TList is limited to only 134,217,727 (MaxInt div 16) items. So TList won't work.

---

Conclusion:
I choose Arnaud Bouchez solution. His TDynArray is impressive! I totally recommend it if you need to process large files.
AlekseyKharlanov's provided another nice solution but TDynArray is already implemented.

Answer 1

Instead of re-inventing the wheel of binary search or B-Tree, try with an existing implementation.

Feed the content into a SQLite3 in-memory DB (with the proper index, and with a transaction every 10,000 INSERT) and you are done. Ensure you target Win64, to have enough space in RAM. You may even use a file-based storage: a bit slower to create, but with indexes, queries by Key will be instant. If you do not have SQlite3 support in your edition of Delphi (via latest FireDAC), you may use our OpenSource unit and its associated documentation.

Using SQlite3 will be definitively faster, and use less resources than a regular client-server SQL database - BTW the "free" edition of MS SQL is not able to handle so much data you need, AFAIR.

Update: I've written some sample code to illustrate how to use SQLite3, with our ORM layer, for your problem - see this source code file in github.

Here are some benchmark info:

  with index defined before insertion:
    INSERT 1000000 rows in 6.71s
    SELECT 1000000 rows per Key index in 1.15s

  with index created after insertion:
    INSERT 1000000 rows in 2.91s
    CREATE INDEX 1000000 in 1.28s
    SELECT 1000000 rows per Key index in 1.15s

  without the index:
    INSERT 1000000 rows in 2.94s
    SELECT 1000000 rows per Key index in 129.27s

So for huge data set, an index is worth it, and creating the index after the data insertion reduces the resources used! Even if the insertion is slower, the gain of an index is huge when selecting per key.. You may try to do the same with MS SQL, or using another ORM, and I guess you will cry. ;)

Answer 2

Another answer, since it is with another solution.

Instead of using a SQLite3 database, I used our TDynArray wrapper, and its sorting and binary search methods.

type
  TEntry = record
    Key: RawUTF8;
    Value: RawUTF8;
  end;
  TEntryDynArray = array of TEntry;

const
  // used to create some fake data, with some multiple occurences of Key
  COUNT = 1000000; // million rows insertion !
  UNIQUE_KEY = 1024; // should be a power of two

procedure Process;

var
  entry: TEntryDynArray;
  entrycount: integer;
  entries: TDynArray;

  procedure DoInsert;
  var i: integer;
      rec: TEntry;
  begin
    for i := 0 to COUNT-1 do begin
      // here we fill with some data
      rec.Key := FormatUTF8('KEY%',[i and pred(UNIQUE_KEY)]);
      rec.Value := FormatUTF8('VALUE%',[i]);
      entries.Add(rec);
    end;
  end;

  procedure DoSelect;
  var i,j, first,last, total: integer;
      key: RawUTF8;
  begin
    total := 0;
    for i := 0 to pred(UNIQUE_KEY) do begin
      key := FormatUTF8('KEY%',[i]);
      assert(entries.FindAllSorted(key,first,last));
      for j := first to last do
        assert(entry[j].Key=key);
      inc(total,last-first+1);
    end;
    assert(total=COUNT);
  end;

Here are the timing results:

one million rows benchmark:
INSERT 1000000 rows in 215.49ms
SORT ARRAY 1000000 in 192.64ms
SELECT 1000000 rows per Key index in 26.15ms

ten million rows benchmark:
INSERT 10000000 rows in 2.10s
SORT ARRAY 10000000 in 3.06s
SELECT 10000000 rows per Key index in 357.72ms

It is more than 10 times faster than the SQLite3 in-memory solution. The 10 millions rows stays in memory of the Win32 process with no problem.

And a good sample of how the TDynArray wrapper works in practice, and how its SSE4.2 optimized string comparison functions give good results.

Full source code is available in our github repository.

Edit: with 100,000,000 rows (100 millions rows), under Win64, for more than 10GB of RAM used during the process:

INSERT 100000000 rows in 27.36s
SORT ARRAY 100000000 in 43.14s
SELECT 100000000 rows per Key index in 4.14s

Answer 3

Since this is One-time task. Fastest way is to load whole file into memory, scan memory line by line, parse key and compare it with search key(keys) and print(save) found positions.

UPD: If you have sorted list in source file. And assume you have 411000keys to lookup. You can use this trick: Sort you search keys in same order with source file. Read first key from both lists and compare it. If they differs read next from source until they equal. Save position, if next key in source also equal, save it too..etc. If next key is differ, read next key from search keys list. Continue until EOF.

Answer 4

Use memory-mapped files. Just think your file is already read into the memory in its entirety and do that very binary search in memory that you wanted. Let Windows care about reading the portions of file when you do your in-memory search.

• https://en.wikipedia.org/wiki/Memory-mapped_file
• https://msdn.microsoft.com/en-us/library/ms810613.aspx
• https://stackoverflow.com/a/9609448/976391
• https://stackoverflow.com/a/726527/976391
• http://msdn.microsoft.com/en-us/library/aa366761%28VS.85.aspx
• https://msdn.microsoft.com/en-us/library/windows/desktop/aa366537.aspx

You may take any of those sources for start, just do not forget to update them for Win64

http://torry.net/quicksearchd.php?String=memory+mapped+files&Title=No

Answer 5

A method that needs the file to be sorted, but avoids data structures entirely:

You only care about one line, so why even read the bulk of the file?

Open the file and move the "get pointer" (apologies for talking C) halfway through the file. You'll have to figure out if you're at a number or a word, but a number should be close by. Once you know the closest number, you know if it's higher or lower than what you want, and continue with the binary search.

Answer 6

Idea based on Aleksey Kharlanov answer. I accepted his answer.
I only copy his idea here because he did not elaborate it (no pseudo-code or deeper analysis of the algorithm). I want to confirm it works before implementing it.

We sort both files (once).
We load Big file in memory (once).
We read Small file line by line from disk (once).

Code:
In the code below, sKey is the current key in Small file. bKey is the current key in the Big file:

LastPos:= 0
for sKey in SmallFile do 
 for CurPos:= LastPos to BigFile.Count do 
  if sKey = bKey 
  then 
    begin 
     SearchNext  // search (down) next entries for possible duplicate keys
     LastPos:= CurPos
    end
  else 
    if sKey < bKey 
    then break

It works because I know the last position (in Big file) of the last key. The next key can only be somewhere UNDER the last position; ON AVERAGE it should be in the next 440 entries. However, I don't even have to always read 440 entries below LastPos because if my sKey does not exist in the big file, it will be smaller than the bKey so I quickly break the inner loop and move on.

Thoughts?

Answer 7

If I were doing this as a one-time thing, I'd create a set with all the keys I need to look up. Then read the file line-by line, check to see if the key exists in the set, and output the value if so.

Briefly, the algorithm is:

mySet = dictionary of keys to look up
for each line in the file
    key = parse key from line
    if key in mySet
        output key and value
end for

Since Delphi doesn't have a generic set, I'd use TDictionary and ignore the value.

The dictionary lookup is O(1), so should be very fast. Your limiting factor will be file I/O time.

I figure that'd take about 10 minutes to code up, and less than 10 minutes to run.