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:
- 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