Best way to save lots of small values (in a database)?

[Vantage]

Hello!

I'm working with a project that collects sensor data from up to 50 sensors every second. The data is small, only a byte/sec. Every second the application produce 50 * 1 byte = 50 byte's that I'm supposed to store.


Today I have solved this in this way:

During run-time I have an array with sensor objects, every object holds information about the sensor and performs some calculations. The object holds an array with bytes that increases with one byte every second. This is managed by .NET.

When I'm finished, I save every array from the sensor objects to a text file on the hard drive, I set the file name to a GUID. I store the GUID in the object and then I save the object information to a database. When I need the sensor data I read the GUID from the database and find the corresponding file...


This approach work of course, but is it the best way to go? The reason I did this was because it felt strange to save 1 byte to a new row in the database. For every 1 byte I need a unique identifier, right?? Felt like a lot of over-head?

Or is there good ways of storing lots of small values produced every second in a database?


Best regards/
Magnus

[TkTech]

Well, how accurate does the sensor read out need to be? Is this for heart surgery or a battlebot? If absolute precision isn't necessary, I would just collect samples and save the averages - ie: every 5 seconds as opposed to every second.

Plus, each row in a mysql db doesn't require very much memory. Having only 1 byte per row is perfectly fine, as long as you don't exceed 2,147,483,647 entries...

[Jordan]

I agree, saving one byte per row is fine and a better option than saving to a text file. You say a lot of overhead but it sounds like you have just as much overhead writing to a text file and then to the DB as well. How far back do you need to save data? Do you need data from 30 seconds ago?