Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
buzibuzi
Starting Member
3 Posts |
 Posted - 2011-05-25 : 10:54:34
|
| Hi,I have the following data that needs to be stored on my database:1.200 users in different locations.2.each user has an average of 5 sensors (total of 1000 sensors all in all)3.some users are joined by a region.4.each user delivers data from his sensors on an daily basis (around 120 records per day from all sensors in a - once a day single transaction)5.so lets say an average total of 24,000 records per day stored in the database;720,000 a month, 8,640,000 a year. i need to plugin a few ASP.NET web graph components on the front end for each user to see his data on graphs.bare in mind the following:1. there is a regional supervisor that can view graphs representing data from 10 users at one time.2. the graphs need to go back a few months in presenting the data.3. i need to perform some complex mathematical functions on the data from a few sensors at a time when presenting it.4.i don't need to cross reference all sensors at once, that data is not relevant. only up to a level of a region.So my question is:to achieve best performance, what kind of tables should i build ?1. one single table of all records archived every few months ?2. a table for each sensor (making it 1000 tables) ?a mixed solution of the above ?Your help is Appreciated |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-05-25 : 11:06:45
|
| I would consider either a single table or a partitioned table, but would partition by user rather than by sensor.1000 tables will be an issue when it comes to maintenance/upgrades to the table structure. |
 |
|
|
buzibuzi
Starting Member
3 Posts |
Posted - 2011-05-25 : 14:39:58
|
how does one implement a partitioned table ? i have never heard of it.thanksquote:
Originally posted by RickD
I would consider either a single table or a partitioned table, but would partition by user rather than by sensor.1000 tables will be an issue when it comes to maintenance/upgrades to the table structure.
|
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
|
|
buzibuzi
Starting Member
3 Posts |
Posted - 2011-05-26 : 05:13:12
|
| thanks RickD :) |
|
|
|
|
|
|
Need help with Database design for best performanc