skatamatic 371 Practically a Posting Shark

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.

Table Type 1 (Our current table)
ID - Primary Key Auto Incrementing Integer
JobIndex - SmallInt
Data0 - Double

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.


Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.