I'm using the sqlite-node extensively to store and retrieve sensor data. So far I have around 20 different sensors (temperature, humidity, co2-concentration, air pressure, open/closed status,...) that push data into a sqlite3 database. The database is stored on a fast ssd and currently has around 260 MBytes of size (data has been collected for the past 2 years).
Every 30 minutes I update my dashboard charts with the collected data from the sqlite3 database, presenting the last 18 hours of data. It takes around 600ms for each data series to be retrieved via the sqlite node. In total around 40 seconds of cpu time is needed on one core to retrieve all data I want to display.
Now here is my problem: While the sqlite node is working the node-red flows are completely locked up and I see that the node-red process is busy 100% on one of the 4 cpu cores. During the time of the data retrieval I can't open dashboard or the flow editor.
Is there anything I can do to keep node-red responsive while the sqlite node is busy? The only idea I have right now is using the exec node and manually using the sqlite3 cli program. This way a separate process would be spawned that can run on another core/thread.
Is there any other more convenient way?
I'm using Node-Red 2.2.2 with the node-red-node-sqlite 1.0.3 node.