I have 100K records table that record activities of users. I want to show some charts base on that data value per time unit. So I've created a chart base table.
Chart table have 3 columns
- Time unit (primary key) column (1,2,3,4...)
- Absolute value per time unit - example: 1, 2, 7, 20 ...
- Accumulated value (and growing) per time unit - example: 1, 3, 10 , 30 ...
Which is better?
- Update Trigger (when status change) - ... I assume that CPU work light only on specific record on every change to the table. Accumulative value will be pass to the next time unit by internal select.
- Aggregation Updating each time unit (by schedule) - ... I assume that CPU work hard on all records. Accumulative value will be calculated on all records till current time unit.
So which approach is make more sense on real time info charts?