Ignition Query Alarm Journal causing gateway crash

Ignition
, ,
1

Hi all,

When running the system.alarm.queryJournal() function the gateway’s memory starts to fill up , which then leads to the gateway crashing.

Is there a performant way to use this function in order to stop this from happening?

I have notice, when running a manual SQL query using the uuid ,the query time is long but
when using the uuid with the timestamp it 's much faster.

Bellow is an example the code I’m trying to excute

date= '2022-05-18 13:44:06'
uuid='3a5c0e5d-a649-4f9c-96a6-954b0274b44c'
javaDate=system.date.parse(date,'yyyy-MM-dd hh:mm:ss')
data = system.alarm.queryJournal(startDate=date,all_properties=[
					('EventId','=',uuid),
					("EventTime", "=", javaDate,
					("Priority","=","High"),
					)])
print data
2

Try putting an index on the date field in the table the alarms are logging to. Some of the Ignition calls perform a lot better if you add an index to the date and include the other fields on the index.

3

Would something like this work? CREATE INDEX uuid on ON alarm_events(eventid,eventtime)

4

It might vary by SQL version. I typically use MSSQL Server and I just create an index on the date field in SQL Management studio and tell it to include the other fields in the index. It makes for a significant performance improvement.

5

what fields do you include?

6

Basically all of them. The idea is that you’re creating an index in the database that allows it to quickly retrieve data for records that have a date within a range. So you’re telling the database to have an index that tracks those fields in relation to the date field. It makes the database bigger but allows it to retrieve data faster when you’re filtering by date which you are when you’re querying that data.

I’m not certain that the way I’ve been doing it is the best way but it is really clear that it makes the historic query methods in Ignition perform a lot faster.

7

Uh, no. Index the fields that you expect to find in where clauses or in the ON clauses of joins.

8

That’s correct.

I wasn’t trying to say to index all fields. I was saying to index the date field and link the other fields to that index.

I almost exclusively work in MSSQL. What I do is I add a non-clustered index to the date field on the table. Then I add the other fields that would be returned on queries against that filter to the “included columns” tab. My understanding is that makes it faster for the db to return the columns when the query because of internal stuff that happens on the database.

That is a good clarification to make sorry for any confusion.

PS. @pturmel knows what he’s talking about with this. It was one of his posts that gave me the idea to index the table and it worked for me. Feel free to correct me on anything or explain it better :slight_smile:

1 Like
9

Would the index changes improve the runtime of system.alarm.queryJournal() function?

10

Yes.