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 |
|
nicolai
Starting Member
1 Post |
 Posted - 2006-02-15 : 08:48:11
|
| Hi everyone,I am currently working on a project where we have to process a great deal of data over a long period of time, and thus we want to introduce some aggregation in order to make the response times of UI (for end-users) queries reasonable. The problem is that I do not have that much experience dealing with amounts of data this big before, and I therefore hope that some of you out there have some good advice or can point to some articles which concern the problem I'm facing.The application is used to capture simple readings every minute from a lot of different devices. These readings consist of an ID, a value and a timestamp, so I am not that much worried about the disk space. The problem is that there could be arbitrarily many devices each turning in a reading every minute (most likely not synchronized). All these "real-time" data need to be stored in a table as I see it. End-users have the possibility of viewing these readings based on some interval (minute, hour, day, week, month and year) and in order to avoid summing all the "real-time" data on the request, I would like to have some triggers (or jobs maybe) which continuously (or when needed) calculates these sums so the application can just select from the table which matches the selected period.Example:If the end-user wants to view his/her readings for the past week, the application need not sum all the values in the minute table, but simply select the value from the correct row in the week table.My first thought was to create a table for each of these periods, and create triggers (or jobs) which propagate the sums upward once enough values are available to fill the next period. By this I mean the week table would be updated once 7 entries become present in the day table.I hope that some of you out there can point me in the right direction, or possibly come with some input that can help me make the right decisions in designing this application.Thanks in advance,Nicolai |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2006-02-15 : 09:05:45
|
| What sort of numbers are you talking about?Mark |
 |
|
|
|
|
|
|
|
Database design (aggregation)