I'm looking for some input on how to keep certain benefits of both a binary file and a SQL database in an interesting data storage problem.
I have a legacy custom binary file format that is essentially several hundred multi-dimensional arrays that are serialized to a file with fwrite()'s in C. Each file is a fixed size (~5MB uncompressed), and represents, for the sake of discussion, one "modeled system". Each modeled system has its own file. So the legacy system is just a bunch of 5MB files.
We need to access this data in two very different ways (binary and SQL).
One use scenario feels like a traditional business app with a SQL back end, where users would need certain values retrieved and displayed (and some updated) as part of a web app. Looking at the data's tabular structure, it has an obvious path toward being converted to a standard SQL database.
However, there are also times when we need to load pretty much one whole 5MB "modeled system" into memory and perform some complex iterative calculations that use most of the values and update certain other values in the modeled system. We can't hit the database inside the loops for the calculations - it is way too slow. Here the need runs against SQL -- the speedy fread() from a binary file is perfect. It puts all the data into the arrays, ready for the complex calculations, and the result is easily serialized back to file with fwrite().
A few more relevant points: First, we only do these calculations about 10 times on one modeled system. That system is effectively read-only from then on, and is eventually deleted. The "modeled systems" do not interact with each other, and a user only works on one at a time. So our data structure is very highly segregated on a per-"modeled system" basis, in effect a database of identically structured databases. Also we do not need huge scale. Under 30 modeled systems is all that would be active at once.
How best to store such data in the back-end of a web app?
All SQL, and no binary files. This requires that the calculation algorithm be re-written to work on the giant "god-object" from the SQL database, rather than all the existing arrays.
Keep the binary files, and after each round of calculations, export the pertinent data (~35%) to SQL for ready access when users need to peruse it via a web app. The problem is keeping the binary files in sync with the SQL tables, especially when a user edits certain SQL values via the web app, rendering the binary file out of date.
Stick with binary serialized data and (somehow) pull data from that binary store but coding that provider for the web app seems ugly.
There must be a better way.
Edit: We have been down the C#/SQL/EntityFramework route for a small subset of data, and fetching data from the database has been surprisingly slow. (Many seconds just for the small subset.) Remember we have hundreds of multi-dimensional arrays that cross-cut the natural but very deep OO model that a web app would want. Having populated the OO model, it would leave us having to re-code the calculation portion.
The replies and comments so far have helped expose the need to consider separately the in-code model from the storage itself. In turn the issue is now how to make that transfer (a) fast, (b) smart about updating values and (c) a reasonable task to code. It seems everyone sees relational (SQL) storage as the way to go.
So, how do I get data in and out of SQL fast(er)? Most of the hundreds of arrays are 3-5 dimensions (of ~10 dimensions in the problem space) that amount to foreign keys. This will set me up with a SQL table for each unique combination of those keys, plus as many fields for values as needed. I'm not locked to the MS stack.
I'll try not have any further scope creep. Thanks!