This made the really expensive query run only once per hour (scan_interval: 3600)
Quite recently, the SQL integration using configuration.yaml was deprecated and the configuration automatically imported to be a GUI-only integration.
Log entry:
Configuration of the SQL sensor platform in YAML is deprecated and will be removed in Home Assistant 2022.6; Your existing configuration has been imported into the UI automatically and can be safely removed from your configuration.yaml file
The import (“Your existing configuration has been imported…”) did not include the scan_interval property. So the expensive SQL was running at least once per minute instead of once per hours causing performance issues. There is currently no way to “see” current scan_interval setting for a given SQL in the GUI, and also there is no longer a way to set it (since the property in configuration.yaml is no longer used).
Make the scan_interval setting available in the GUI
Don’t deprecate / import the .yaml-code before the new GUI is fully supporting all that was previously possible in .yaml, as that would be a breaking change. Better to wait and fix before breaking something that was working (a.k.a. don’t fix what aint broken)
The devs have stated quite a while ago that scan_interval is being deprecated because it was being abused to hammer devices. But there is a way to solve your problem and possibly even improve your process:
Disable polling from the Devices and Services page (3 dot menu on the device or service>System Options>turn off “Enable polling for updates.”), then use an automation to update the entity.
Example Automation:
alias: Update some SQL Sensors on the hour
description: ''
trigger:
- platform: time_pattern
hours: /1
action:
- service: homeassistant.update_entity
data: {}
target:
entity_id:
- sensor.sql_select_query_1
- sensor.sql_select_query_2
- sensor.sql_select_query_3
mode: single
Note: I haven’t tested this exact automation because I don’t use sql sensors, but it should give you an idea of what you need to do.
Automation-based updating is actually much better than the simplistic scan_interval. For instance, I use a similar automation for another type of sensor I manually want to poll at different intervals depending on some conditions - e.g. if something is in one particular state, I actually do need to bang on something else every 20 seconds, but if the first thing is in any other state, I only want to hit the second thing every hour or even less.
I will try this out, I didn’t know about the possibility to disable polling. That option is not there for all integrations. Thanks! (Not really a solution though, but a workaround, see below)
Regarding the deprecation (rather the reasons behind it), I don’t really agree with the devs in this case.
Scan interval is being deprecated, as it was being abused to hammer devices. The integration decides the optimal scan interval for devices.
In my opinion, Home Assistant is not a kindergarten. If someone wants to misconfigure or spam their devices it should be up to them. Of course with a sensible default value. And, in this case, there should be an option to decide the refresh frequency in the GUI, as the GUI is the only place one could configure this integration and the integration in itself cannot possibly decide on a reasonable refresh frequency (or scan_interval) as some queries would be good to have running once per 20 seconds and another once per hour or even less frequent. So the user should actually decide. And it was possible to decide before.
I agree with you, for your use case. I.e. there are situations where automation-based updating makes a lot of sence when you want to have a variable refresh rate, or not refreshing at the same time as something else, etc. For my use case, I dont really care. As long as it is run roughly once per hour it is fine. And in that case I think automation-based updates is a lot of overhead.
If someone cannot handle setting reasonable values for scan_interval, then perhaps that someone should use Homey or some other closed-down impossible-to-make-mistake system
Regarding it not being there for all integrations, I’m curious which ones you’re missing it on? I just looked at the 33 integrations I have setup via GUI, none that are using local polling or cloud polling were missing the option. You can tell the what class your integrations are by visiting the documentation from the same 3-dot menu and visiting the “Documentation” link and looking for the IoT device class in the details in the upper right corner of the page (this should exist for all core integrations). For instance Hue is “Local Push”: Philips Hue - Home Assistant. Anything that is Cloud Push or Local Push wouldn’t use polling, so no need for a switch. If there are core polling integrations available for GUI setup that don’t allow disabling the polling so that we can control it ourselves, we should open an issue on them
Regarding your other concerns: In my case, not with sql(since I don’t actually have any need for them), but with other sensor types, it did take me a bit of time to adjust to the change when moving away from scan_interval, but I haven’t felt limited by the change or like the overhead was large. Most integrations don’t require adjusting away from their defaults, but I don’t feel like I’ve been treated like Kindergartner because of that. Quite the opposite, with the move away from the naive scan_interval and the option to disable the polling and control updates myself, they’ve empowered me to make those decisions and do it flexibly. It’s just done from a different place in the gui (e.g. not where the device/entity is configured, but in an automation). It could be better documented though, no doubt!
Integrations can also be made (by the devs) to intelligently adjust the polling, btw. For instance, I use the custom NFL integration that will poll very frequently during live games of configured teams, but very infrequently the rest of the week, and it “just works™”, no custom setup work or any automation needed to benefit from that.
sql is one specific case where, as you stated, one might want very different polling periods for different types of queries, and that’s actually why I feel like the flexibility to set them up in the gui, with an automation to adjust the poll period actually works better than configuring in yaml. That includes no longer needing to restart the core if you need to make a change to the period. Just to be clear, when I say it’s better, for sql specifically I am not addressing the problem with very large queries and formatting them in the smallish text box…that’s really a whole other topic
I can confirm that the workaround with manually updating the sensors with homeassistant.update_entity after disabling the polling on the SQL integration did in fact work.
Around half of my integration does not have a disable polling switch, but, as you said, they were push integrations. My bad (and, I didn’t even know about that switch anyway, before you told me!). I sometimes do see a use case for manually updating something that is “push”. Some of my power monitoring plugs are updating their state like every 5 seconds, filling the states database with useless over-information.
The workaround in that case is to create a custom template sensor extracting what you want and using a trigger to make sure that the template only refreshes with whatever frequency you find suitable. And then disable the original device / entity from the recorder.
I agree. Home assistant restarts are way to necessary these days. But I would still prefer having either a “trigger” section or a “scan_interval” section in the gui for each query. What would be even better is to allow for native SQL queries from automations. I.e. be able to state the query directly in an automation/script. And also enable other queries than SELECT.
