I work for an instrumentation company as an in-house software developer. We are thinking of restructuring our database that we use to record data. I am wondering what would be more efficient.
Basically theres an array of sensor values
Starting at Data0 and ending with data appended with the sensor count (Data16 in a 17 sensor system). At the moment we just save all the sensor values in their own columns, but in order to have better scalability I think it might work better to use only 2 columns - 1 for the sensor's ID or index and 1 for the sensors data.
IE
Table Type 1 (Our current table)
ID - Primary Key Auto Incrementing Integer
JobIndex - SmallInt
Data0 - Double
...
Data255
Table Type 2 (My Idea)
ID - PK Auto Inc INT
JobIndex - SmallInt
SensorIndex - SmallInt
Data - Double
The main reason that I want to switch is because usually we only use 6 sensors or so, but want to be able to use infinite number of sensors. Table 1 has a whole lot of columns that hardly ever get used (ie 0 filled or NULL). Table 2 it doesnt matter how many sensors you use (assuming that number is not greater than a SmallInt's max). I am just wondering what the performance difference might be between the two. A new record is added every 100ms, and up to 24 hours of recording must be able to be loaded in a very short amount of time. Of course Table 2 will have many more entries, but it makes sense to do a Select * From Table2 Where JobIndex=@MyIndex Order By SensorIndex ASC to get my records from it then parse it in code based on index afterwards.
I am a bit new to the database optimizing realm - maybe someone with more experience than me can help me make the decision.
Thanks!