I am working on a data comparison tool in C#. It takes data from two systems, stores them in a local cache (with a unique primary key, some XML and a record number) and run some checks afterwards.

What I have right now is:

  • I create a table in LocalDB for every system I get some data from.
  • I load a dataset from system X, create a key out of it, convert the data into an XML string and put it with a record number into its table in LocalDB (let's say "Y").
  • Once all the data from both systems are loaded, I run one of the following checks: AequalB, AllAinB, AllBinA, AllAnotInB, AllBnotInA
    • This check is based on the keys (e.g. "Every key from system A has to be in system B and vice versa").
    • So I load an ordered bunch of 1,000 records for both systems and compare them until all keys are checked.
  • Afterwards I go through every record in "Y" and load the same object from another table by its key.
    • I run some checks with the XML from both systems.
  • Once all the checks for all the records are done, I simply drop the table.
  • There may be multiple of those tasks running in parallel.

The problem is LocalDB. The "Maximum relational database size" is 10GB (see https://msdn.microsoft.com/en-gb/library/cc645993.aspx#Cross-BoxScaleLimits). This is perfectly fine for a few hundred thousands of records per system but not millions. The goal would be to run the whole comparison with up to 100 million records per system.

Originally I planned to run it all in-memory. But this idea was abandoned and replaced by a "minimal memory footprint in the application"-policy. I am stuck in a 32bit ASP.NET application which allows approximately 1GB of RAM per process.

For now I have two ideas I'd like to give a try: Redis and SQLite. Do you have other suggestions which products might be suitable for my needs? Of course, it should be free and be allowed to be distributed together with our product.

A colleague of mine suggested to take a look into some big data tools. But I don't think that this is worth the time. Big data lifes from mapping many records by some criteria. If I am not mistaken it is not suitable for many single search requests.

Best regards, Carsten

  • 1
    Why not storing the xml string into files and use the LocalDB only for referencing the path to the file? In your case with 100m records you can use one XML File for e.g. 10k records and u get 10k XML Files. – Sebastian Siemens Jan 16 '17 at 16:16
  • You could compute a hash, say MD5 or SHA-1, on the XML for each entry and store that, with the id, this should cut the storage requirements drastically and, while you would have to hash each record the compare would be a lot faster. – Steve Barnes Jan 16 '17 at 19:24
  • @Sebastian and Steve Interesting idea. But I am a bit afraid that I would hit the 10GB limitation if there are 200m records (100m per system). I have a database which I use as a system to load the data from. There are exactly 10m records in it. The database file is around 7GB. So I have to get rid of the LocalDB at all. – user28993 Jan 16 '17 at 19:49
  • By the way: "the compare would be a lot faster". It would if I would compare the whole object only. But the configuration allows much more, like compare field1with field2 or field2 with field3 + " - " + field7 and so on. – user28993 Jan 17 '17 at 7:34

Your Answer

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