Arrays in a database

[skatamatic]

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!

-Note: I already posted this in the Database forum, but I think this forum is a lot more active. If an admin has a problem with this, lemme know.

[nick.crane]

I had to do something similar myself.
I think that your table 2 idea is better.
Better scalability, greater flexibility for searching.
With the correct table indexing performance should not be an issue.
For a whole lot of ideas on databases and SQL I generally go to http://www.sqlservercentral.com/

I find the stairway series they do most useful.
E.g Stairway to SQL Server Indexes

[skatamatic]

I had to do something similar myself.
I think that your table 2 idea is better.
Better scalability, greater flexibility for searching.
With the correct table indexing performance should not be an issue.
For a whole lot of ideas on databases and SQL I generally go to http://www.sqlservercentral.com/

I find the stairway series they do most useful.
E.g Stairway to SQL Server Indexes

I think it's a better idea too. Although my boss is skeptical (me and him and a web guy are the whole software department). He is worried that it will lose readability and efficiency. I told him databases aren't supposed to be readable to people haha. I will look into well designed indexing to improve the performance - thanks for the links. If anyone has any tips regarding indexes for this particular table feel free to leave them here!

[rikthefrog]

Hi I'm doing a little bit of programming but know a lot of databases. I think the second table is the best solution. From performance point of view it takes a little bit more to insert multiple records however, as you say that it are normally 6 till 18sensors this is not a real problem. My only concern is that you have a primary key on some autonumbering, are you using this one to retrieve the sensor? If not you need to set a index on your search column. However ifthis search column, jobindex is unique together with your sensorindex it is maybe an idea to put an primarykey on both the fields. To retrieve data with these primary key will not give you much performane loss other that you recieve 8 records back with 8 sensors.

Your second solution makes you more fleible, however the first one is definetely faster to retrieve and to store. Less data and only once the overhead to to create a record. Also retrieving 1 record is normally faster. This normally also how datawarehouses are working. One big record with loads af columns, as dta warehouses have to load big amounts of data and report on big amounts of data.