6

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?

Some ideas:

  • 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.

Thanks!

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!

Improve this question
7
  • Are these calculations against several of these 5mb data units or just one? 5mb is peanuts. Why not load everything into your memory before the calculation starts? I can hardly imagine a database would take more than a second to do this.
    – Teimpz
    Jun 3, 2016 at 5:49
  • 1
    I don't know if you can use it, but ORM maybe usefull, it usually create some proxy that will take all modifications without hitting the database as long it is not needed (ie : commit, or query with some criteria). This can require quite some thought but it can be worth. If you load all your database in memory, you should be able to perform all your process without hitting the database as long you don't commit.
    – Walfrat
    Jun 3, 2016 at 8:42
  • I don't see why keeping the data in a database requires the calculations to take place in a db.
    – JeffO
    Jun 3, 2016 at 14:08
  • @Teimpz : The calculations are all inside the 5MB. See my edit about slowness fetching data.
    – Ian W
    Jun 6, 2016 at 19:46
  • 1
    Are you sure you configure it properly ? Like not doing the n+1 select thing when fetching relations and so on ?
    – Walfrat
    Jun 7, 2016 at 6:57

2 Answers 2

Reset to default
4

Put the data in a data base.

Write a function which pulls all the data from the database and populates the arrays.

Pass the populated arrays to the unchanged calc function

Write a function to write a database using the resultant arrays from the calc function.

Improve this answer
4
  • Sounds good but a big task. Any advice on how to make those functions fast given my arrays of values as described in the edit?
    – Ian W
    Jun 6, 2016 at 19:51
  • use the origonal calc function so it should be the same speed
    – Ewan
    Jun 7, 2016 at 12:03
  • I was referring to the "functions" in your answer that would pull from the db and populate arrays.
    – Ian W
    Jun 7, 2016 at 12:21
  • i would go with simple select * from table datareaders which you then iterate. should be as fast as you can get, same or better than reading from a disk. obvs depends on the structure of your arrays and data though
    – Ewan
    Jun 7, 2016 at 13:01
1

5Mb seems like a trivial amount of data these days. So I wouldn't worry about pulling that out on demand, deserialising and manipulating that. Performance shouldn't be a problem given your quantities of data

If there are no projected format changes for this data, I would stick to this approach.

Improve this answer
3
  • Do you mean keep it in relational database? Or serialized binary data?
    – Ian W
    Jun 6, 2016 at 19:53
  • Keeping it as is
    – Brian Agnew
    Jun 7, 2016 at 9:27
  • I'd love to do that. The challenge is that I need to provide data to support a web app too. Any tips on patterns for using serialized binary data rather than SQL for that?
    – Ian W
    Jun 7, 2016 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.