0

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

  1. Time unit (primary key) column (1,2,3,4...)
  2. Absolute value per time unit - example: 1, 2, 7, 20 ...
  3. 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.

OR

  • 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?

0

How many times will be a row updated ? How many rows in percentage are updated per Aggregation ? Trigger is a possible solution for a reduced number of updates , aggregation is a much better solution if large percentage of the table is updated .

1
  • In 12 hours , each row of the 100K rows can be updated 3 to 10 times. By trigger, it will effect the chart table about 1M times (updating one at a time). By schedule it will effect the chart table once in 30 sec, this mean that the aggregation will be once in 30 sec on whole 100K records. May 19 '18 at 9:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.