Slow charts

[BNNorman]

I noticed that some sensors take a long time to display the chart whereas the new UKA00450-02 (Freetown) is very quick. I'm using Firefox on Windows 10. This suggests that an SQL query or technique is at fault.

I set Firefiox in network mode - you can see the results here ...

It looks like the list-reading-types action is the culprit so, on my Pi 3b+, I did some query tests. Initially I used DISTINCT then, as you do, I used GROUP - that made little difference on the last two queries below. So not that cause. With no date restriction the query I used took 5.56s and with a date limitation I got it down to 0.38s

# no date restriction

MariaDB [aq_db]> select distinct short_descr from reading_value_types join reading_values on reading_values.reading_value_types_id=reading_value_types.id join readings on 
>readings.id=reading_values.reading_id where readings.device_id=38;
+-------------+
| short_descr |
+-------------+
| temperature |
| PM10        |
| humidity    |
| pressure    |
| PM25        |
+-------------+
5 rows in set (5.56 sec)

# date restricted to last two days
Selecting readings seen in the last day
MariaDB [aq_db]> select distinct short_descr from reading_value_types join reading_values on reading_values.reading_value_types_id=reading_value_types.id join readings on 
> readings.id=reading_values.reading_id where readings.device_id=38 and datediff(now(),s_or_r)<=1;
+-------------+
| short_descr |
+-------------+
| temperature |
| PM10        |
| humidity    |
| pressure    |
| PM25        |
+-------------+
5 rows in set (1.20 sec)

# date restricted to today still using distinct

MariaDB [aq_db]> select distinct short_descr from reading_value_types join reading_values on reading_values.reading_value_types_id=reading_value_types.id join readings on 
>readings.id=reading_values.reading_id where readings.device_id=38 and datediff(now(),s_or_r)=0;
+-------------+
| short_descr |
+-------------+
| temperature |
| PM10        |
| humidity    |
| pressure    |
| PM25        |
+-------------+
5 rows in set (0.38 sec)

# date restricted to today using GROUP

MariaDB [aq_db]> select  short_descr from reading_value_types join reading_values on reading_values.reading_value_types_id=reading_value_types.id join readings on 
>readings.id=reading_values.reading_id where readings.device_id=38 and datediff(now(),s_or_r)=0 group by short_descr;
+-------------+
| short_descr |
+-------------+
| humidity    |
| PM10        |
| PM25        |
| pressure    |
| temperature |
+-------------+
5 rows in set (0.40 sec)

What I do notice is that your list_device_types query doesn't limit the data by date. I do this to restrict the values returned to today (the default display).

In my first attempt the query took 5 seconds but in subsequent queries where I used datediff() it came down to less than half a second

This, I think, explains why some charts take a lot longer to render.

Furthermore, you can detect how long ago the last reading was and choose the default chart display accordingly using min(datedif(...)) as below

# my sensor - still active
MariaDB [aq_db]> select  min(datediff(now(),s_or_r)) from readings where device_id=38;                                                                                                         +-----------------------------+
| min(datediff(now(),s_or_r)) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.37 sec)

# freetown UKA00450-01 inactive for the past week or so
MariaDB [aq_db]> select  min(datediff(now(),s_or_r)) from readings where device_id=29;
+-----------------------------+
| min(datediff(now(),s_or_r)) |
+-----------------------------+
|                          12 |
+-----------------------------+
1 row in set (1.38 sec)

I have an old copy of the aq map on my Pi - it takes a similar length of time to display the chart for my device.

I understand you want to provide a button list of all types to click on but maybe this can be derived from the device_sensors table by splitting the description. For example a device with BME280 and SDS011 has Temperature, Pressure, Humidity, PM2.5 and PM10

MariaDB [aq_db]> select * from sensors;
+----+----------+-----------------------------------+
| id | Type     | Description                       |
+----+----------+-----------------------------------+
|  1 | BME280   | Temperature,Pressure,Humidity     |
|  2 | BME680   | Temperature,Pressure,Humidity,VOC |
|  3 | SDS011   | PM2.5,PM10                        |
|  4 | PMS7003  | PM1.0,PM2.5,PM10                  |
|  5 | NEO-6M   | GNSS                              |
|  6 | DHT11    | Humidity,Temperature              |
|  7 | BMP280   | Pressure,Temperature              |
|  8 | DS18B20  | Temperature                       |
|  9 | MICS6814 | NO2,CO,NH3                        |
+----+----------+-----------------------------------+
9 rows in set (0.01 sec)

Currently, these strings match the reading_value_types short_descr - with the exception of PM2.5 which is recorded as PM25, but I can change that in no time.

Perhaps we can discuss this at the next meetup on thursday. Sorry it's such a long post.

[sbrl]

No problem! Definitely looks like you've done your research on this one. Some improvements to make on the existing system, to be sure!

Yeah, let's discuss at the hw meetup tomorrow.

[BNNorman]

Maybe a stored procedure would help? Something to identify if there are readings in last day/week/month etc. This would also help with choosing the active date range button on the chart.

[BNNorman]

I have added a new column to devices 'last_seen' which is updated when new records are added so it should always show the latest s_or_r. That should speed a few things up when you get around to modding your code.

[sbrl]

Implemented! Thanks for that @BNNorman. Try it in beta: https://sensors.connectedhumber.org/beta/app/

If it's good for you, I'll push that and the other performance improvement we made recently to stable in 1.13.2.

[BNNorman]

Just tried a handful of sensors, very much quicker. Go for it.