Time Series Chart not refreshing

Ignition
1

I have a Perspective Time Series Chart with five series’ on it (2 plots with some limit lines). I am trying to get “Realtime” functionality like the Easy Chart. To achieve this I have set a 60 second polling rate on the data binding for each series array element - which are tied to Named queries.
This sometimes does the trick for a bit, but eventually pens stop polling, and eventually the whole chart goes stale.
In the below example you can see a gap at the end of the top plot, and the whole chart was > 30 minutes old (time when screencap was taken was 12:25).
Any idea why the query polling just stops working?

Details:

  • 8.0.9
  • The named queries return the “last N hours” of records
  • N number of hours comes from the value of a numeric entry field on the View

no%20refresh

2

Hello @jgardner, does modifying the frequency of the polling rate change the behavior for you? As an example, does a 3 second polling rate perform any better than the 60 second polling rate you’re using now? Also, if you bind that data that same named query to another component, does it also drop at roughly 30 minutes?

3

I upped the frequency on the data bindings to 15 seconds. I also added a simple table with the temperature data point. Both the chart and the table stop refreshing - often after a few minutes.

An interesting behavior - when the table is auto-updating, it only does so every 2 minutes. Even though it should be polling at 15s, it will sit for 2 minutes, then

On the graph the minutes are set by an expression tied to a number input component. On the table I hard coded the “N minutes” value to an integer. Thought I would mention the setup in case it matters - but I don’t see how you would have a different setup and still get a “realtime chart” functionality out of the time series chart.

4

That suggests that your database needs optimization. A time-series dataset of even tens of thousands of points should be retrievable in a second or two if there are suitable indices in the DB tables.

5

Editing fail - that section got cut off. What I should have said there is that I have a manual "Refresh" button for testing with the script:

self.getSibling("Table_0").refreshBinding('props.data')

When I push the button, the table refreshes instantly (good DB performance). But if I wait 20 seconds and push it again, it should show the new data point it collects every 15 seconds. But the table doesn't refresh.

If I keep mashing the refresh button, it will eventually work again - but only after 90 seconds - and it will only work once. So essentially, the refresh button only "works" every minute and a half even though I have confirmed I am adding records to the DB every 15 seconds.

6

Heh, happens to us all eventually.

This makes me suspect the Named Query cache. How often are you moving the time endpoints fed to the Named Query?

2 Likes
7

I think you're on to something. I set Bypass Cache to True on the Data bindings for the table and the chart series. They seem to be updating every data point! I'll let it run for a bit and see if it stalls out.

In answer to your question: the named query time period is changed every time the query is polled. Because it is where claused with:

WHERE t_stamp >= DATEADD(MINUTE,- :Last_N_Minutes,GETDATE())

In the test table, I have the :Last_N_Minutes parameter hard-coded to 5 minutes.
Is that what you were asking?

8

Yes. I tend to compute start/end bounds outside the query, with desired granularity, so that their changes can be interpreted by the caching engine. Your query only provides Last_N_Minutes, which doesn't change. I don't think the caching engine is smart enough to detect that GETDATE() yields a different value every time the query runs.

2 Likes
9

Aaaah. That makes sense.

Also, on the Named Query:
caching
Looks like a case of Ignition doing exactly what you ask it to :slight_smile: Not sure when I turned it on since it defaults off.
For any folks new to this, I found the reference at: https://docs.inductiveautomation.com/display/DOC80/Named+Query+Caching

1 Like
10

I would encourage you to leave caching turned on. Then, when computing the timestamps to use for the beginning and ending points, round those timestamps to the nearest 5 seconds (or something like that, smaller than your display refresh). Then when multiple clients are running, they will efficiently share the query cache without any visible impact.

(Note, round beginning down, ending up.)

11

I would like to implement that. Are you recommending to move the GETDATE() based expression to the Binding -> Parameters -> Value equation builder? Is there a better method of calculating the timestamp for “X hours ago”.

12

Yes, you want an expression that takes X and yields a timestamp. That timestamp is the query start time. The named query would look like this:

SELECT *
FROM myTable
WHERE t_stamp >= :startTS

In a script module, you’d have a function like so:

def hoursBefore(X):
	ts = system.date.now().time
	return system.date.fromMillis(ts - (ts % 300000) - X * 3600000)

Then in your named query binding, given a custom property on the chart containing the hours to display, the value expression for startTS would look like this:

runScript("myScript.hoursBefore", 0, {this.custom.HoursToShow})

Have the named query cache for five seconds or so.

1 Like