SQLite Forum

Best way to observe database operations
> On change, re-run query, diff with RAM results.

I don't see the need for the diff. Just repaint with the current info any time the DB changes, as long as the time since last update is greater than the shortest allowed repaint time.

That lower limit should probably be no smaller than about 50 ms, the smallest update rate that a human can readily notice. In specialized cases, humans can be faster, but it is also the case that too-rapid updates are hard for humans to track. Thus [DMMs][1], which rarely update more than 3 times a second.

For some "dashboard" type operations, you have no choice but to recompute the displayed results from the full data set on each repaint anyway. For instance, if you're showing a running average of some parameter, you need to recompute this over the entire history back to the cutoff point when a new data point comes in.

To take a simple case of a regular average:

  sqlite> create table data(n);
  sqlite> insert into data(n) values (1),(2),(3);
  sqlite> select avg(n) from data;

This gives the correct result, 2.

Now append 4 to the data array and recompute:

  sqlite> insert into data values(4);
  sqlite> select avg(n) from data;

My question then to you is, using a diff-based algorithm, how do you take only the 2.0 from the first result and the new data point (4) and still get the new correct average, 2.5? Unless you know some trick of arithmetic I don't, you can't: you need all four data points to compute the correct answer.

By the way, that 12 ms result I mentioned above? That was on a "chilled" DB: not fully cold, but not fully prepared to answer that particular query from RAM, either. Re-running it twice more caused the results computation to occur first in 8 ms and then 7.2 ms.

[1]: https://en.wikipedia.org/wiki/Multimeter#Digital_multimeters_.28DMM_or_DVOM.29