Most efficient way to store data from laboratory instrumentation

Question

Most efficient way to store data from laboratory instrumentation

Wayne Knowles 0

I would like to know the most efficient table architecture to store the following: I have two machine analysing samples. Each machine takes a reading from 3 sensors every second up to a period of twenty minutes and the analysis run is complete. Samples have unique IDs and run into the 10s of thousands. I envisage a table that contains sample information, ID, the machine ID and the date of the analysis neatly in rows with one row per sample. The problems comes with the sensor readings. The number of readings (1/sec) would produce too many columns (i.e. 60x20) for a regular table if the sensor readings were stored in rows for each sample. However, if I stored time and the sensor readings in columns (i.e. vertically), then I would have a lot of data continuously duplicated (The time increment and sample ID).

I am really scratching my head on how best to store these data in SQL table. Could storing the time increments and sensor readings as 'blobs' be a solution?

Thanks

1 answer

Your answer

Answer 1

Erland Sommarskog 120.2K MVP

First question: Have you considered Azure Data Explorer, also known as Kusto. I have not worked with it myself, but I believe Kusto is really great for time-series data.

If there are three sensor, I would start with one column per sensor, particularly if they measure different things. Together with this, you could have the sample ID and the timestamp as the key columns. The sample ID would be repeated, but the timestamp would not. Actually, you could stored the sampleIDs in a separate table with stop and start times for these samples, and then you would not need the sampleIDs in the sensor table.

Or is there something I have missed?

Wayne Knowles 0 Reputation points

2023-08-17T22:10:44.86+00:00

Hi Erland. Many thanks for the response. No I have not worked with Kusto but I will give it a look.

I'm not sure about just saving start and stop times as the times are not related to time as in a time on a clock. They are related to an analysis timer on the machines. So when analysis begins time (t)=0 and measurements are recorded from the three sensors every second for 20 minutes. Therefore there are a total of 1200 readings from each sensor in one analysis run. Individual times have to be recorded as these are used to generate a graph of time (x) against the response from each sensor (y). This is for just one sample and our database contains data from 10's, possibly 100s of thousands of samples. I understand your suggestion of using Sample ID and time as key columns but I just wondered if there was a better way to do this rather than duplicating the SampleID's and 1 second time intervals. Would you like me to post a small example of the data?
Wayne Knowles 0 Reputation points

2023-08-17T22:13:09.2+00:00

Hi Erland. Many thanks for the response. No I have not worked with Kusto but I will give it a look.

I'm not sure about just saving start and stop times as the times are not related to time as in a time on a clock. They are related to an analysis timer on the machines. So when analysis begins time (t)=0 and measurements are recorded from the three sensors every second for 20 minutes. Therefore there are a total of 1200 readings from each sensor in one analysis run. Individual times have to be recorded as these are used to generate a graph of time (x) against the response from each sensor (y). This is for just one sample and our database contains data from 10's, possibly 100s of thousands of samples. I understand your suggestion of using Sample ID and time as key columns but I just wondered if there was a better way to do this rather than duplicating the SampleID's and 1 second time intervals. Would you like me to post a small example of the data?
Erland Sommarskog 120.2K Reputation points MVP

2023-08-18T20:53:06.5333333+00:00

OK, so "time" here will have to be seen as something abstract. I guess that analysis timer still produces something that can be used as a key within the sample?

An important question is how are you going to use the data? You mentioned the alternative of using a blob. And indeed, that that could be a good choice if you are going to do all analysis client-side in a dedicated program. In that case, you may only are are about reading and writing the full set to the database.

But more likely you will run queries over the set. Well, if you only care about aggregates, you could compute the aggregates and then throw the raw data away. But if you are going to look for time series, like for how many consecutive did that sensor give increasing values etc, you want the full data.

And for the queries like you want all data from the same sensor in the same column, else it is going to be difficult.

I understand the concern of repeating the sample ID all though the set, but this is by no means uncommon in a relational database.

One way to reduce the space consumption due to that repetition is to use a columnstore index, where data is stored by column. This saves even more space, would the sensor values vary between a small number of values. However, I think time-series queries will a lot less efficient, so I don't think this is a good choice.
Wayne Knowles 0 Reputation points

2023-08-24T21:16:35.7466667+00:00

Thanks again, Erland. We definitely need to keep the entire set of raw data. The data from the sensors are plotted against time to display issues such as presence of contaminants etc. These fluctuations vary a lot in value and could occurs at different times. Queries would tend to be based on specific sample IDs and their detector signals over time to plot graphs for comparison; not so much for specific signal values for a given sensor. I am thinking that storing the detector signals as blobs may be the way to go.

Share via

Most efficient way to store data from laboratory instrumentation

1 answer

Your answer