Averaging is Back!

[Cube]

New code in venturii_d.php introduced this evening allows SQL to do all the heavy lifting as far as averaging the results, allowing us to peruse minutes, hours, days, weeks, or months' worth of data as single data points to be passed to the graphing library. XRAD accomplished the same thing in a different way, creating individual tables for the averages of minuts, hour, and day and then calculating them as each minute, hour, and day passed. Both systems had their advantages and disadvantages. For example, it was very quick to query the XRAD tables for hourly averages since each query returned only a handful of results. This performance benefit came at the cost of disk and table space, creating half a dozen tables each month to hold the sets of computed values. This new system stores the data once, reducing table and disk space but paying for the optimization in data cost by increasing the CPU hit on retrieving it. Fortunately SQL is quite optimized at processing the kinds of requests we are making of it, and my tests so far on a data set of ~90k records shows the following benchmarks:

Raw Mode: (As before)

Code: [Select]

Processed : 93074 in 2 Graphs
Mysql Time: 0.6328840255737305 seconds
Graph Time: 3.743463039398193 seconds
Total Time: 4.376347064971924 seconds
Data / Sec: ~21268 data / second

Second Mode:

Code: [Select]

Processed : 55800 in 2 Graphs
Mysql Time: 0.8703169822692871 seconds
Graph Time: 2.293694972991943 seconds
Total Time: 3.16401195526123 seconds
Data / Sec: ~17636 data / second

Minute Mode:

Code: [Select]

Processed : 2883 in 2 Graphs
Mysql Time: 0.471541166305542 seconds
Graph Time: 0.1927869319915771 seconds
Total Time: 0.6643280982971191 seconds
Data / Sec: ~4340 data / second

Hour Mode:

Code: [Select]

Processed : 52 in 2 Graphs
Mysql Time: 0.4362599849700928 seconds
Graph Time: 0.1189849376678467 seconds
Total Time: 0.5552449226379395 seconds
Data / Sec: ~94 data / second

Day Mode:

Code: [Select]

Processed : 6 in 2 Graphs
Mysql Time: 0.4037258625030518 seconds
Graph Time: 0.1409800052642822 seconds
Total Time: 0.544705867767334 seconds
Data / Sec: ~11 data / second
As you can see, having SQL average the data before passing it to JPGraph only takes an additional 0.2 seconds on a days' worth of power meter readings for both phases, and in some cases actually returned results in LESS time than it took to fetch the raw results. This brings back the averaging functionality of the XRAD system, makes it retroactive to all recorded VINT data and also applies the capability across all numeric data types. This is a win in my book all around, and the slight performance hit it takes far outweighs any potential downfalls of not storing the averages in separate tables.

From the VINT selector window, the Select Report Resolution field has always been there, but until [today] it really didn't do anything. Now this has been fixed and it again affects the output graphs produced in the way expected by the user.