I am migrating from a mySQL based IoT system to InfluxDB. All is good except for one dashboard item.

The application is logging and displaying data in a solar energy system.

I used a complex SQL statement to access 8 datapoints to display a status statement, such as “Generator will start in about 12 minutes” based on current state of charge and consumption of energy.

when I switched to Influxdb I distributed the datapoints to separate measurements based on the devices in the system. . (inverter, generator, solar panel, battery monitor, etc.)

I’ve got it working but it takes many seconds to generate the statement. most other queries and dashboard graphs happen quickly

My question is two parts:

- Am I doing this all wrong and if so point me down the right path or…
- What can I do to speed this up

As a new user I cannot attach the script.

import "math" // https://docs.influxdata.com/flux/v0.65/stdlib/math/ starttime = dashboardTime // starttime = -2m aggregatetime = 1m from(bucket: "cottage_2020/autogen") |> range(start: starttime, stop: now()) |> filter(fn: (r) => r._measurement =~ /REMOTE|BMK|AGS/ and r._field =~ /socstart|socstop|amph|adc|soc|running|status|status_text/) |> fill(usePrevious: true) |> aggregateWindow(every: aggregatetime, fn: last) // removing last gives me a lot of results, using last restricts it to the most recent |> last() |> drop(fn: (column) => (column =~ /_measure*/)) |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value") |> map(fn: (r) => ({_time: r._time, socstart: float(v: r.socstart) / 100.0, socstop: float(v: r.socstop) / 100.0, amph: float(v: r.amph), adc: float(v: r.adc), soc: float(v: r.soc) / 100.0, usedsoc: 1.0 - (float(v: r.soc) / 100.0), running: r.running, status: r.status, status_text: r.status_text, })) |> map(fn: (r) => ({_time: r._time, description: if r.running == true then if r.status == 13 then "Generator will stop in about " + string(v: int(v: math.abs(x: r.amph) / r.usedsoc * (r.socstop - r.soc) * 60.0)) + " minutes" else "Generator " + r.status_text else if r.soc < r.socstart then "Generator should have started!" else if r.soc >= 1.0 or r.amph >= 0.0 then "All is good" else if r.adc < 0.0 then if (math.abs(x: r.amph) / r.usedsoc * (r.soc - r.socstart) / math.abs(x: r.adc)) > 2 then "Discharging " + string( v: 0.0 - r.adc) else "Generator starting in about " + string(v: int(v: math.ceil(x: math.abs(x: r.amph) / r.usedsoc * (r.soc - r.socstart) / math.abs(x: r.adc) * 60.0 ))) + " minute" else if r.adc > 0.0 then if (math.abs(x: r.amph) / r.adc) > 2.0 then "Charging " + string( v: r.adc) else "Fully charged in about " + string(v: int(v: math.abs(x: r.amph) / r.adc * 60.0)) + " minutes" else "Treading water"} ))