Slow Queries on Alarm Analysis Cloud Template

Ignition
1

Hi all,

A few years ago I used Travis’s excellent Alarm Analysis template. I recently tried importing it again for version 7.9.4+ (7.9.9) and I cannot seem to get it to work properly with most queries timing out. I am using MySQL and have set the alarms data source correctly.

Running the individual queries inside the script console I found that these queries are running very slowly, with the MySQL query taking over 30 seconds to calculate the duration of alarms for a 1.5 hour period:


"SELECT " + 
"	a.displaypath, " +
"	SUM(TIMESTAMPDIFF(SECOND, a.eventtime, COALESCE(c.eventtime, CURRENT_TIMESTAMP))) duration " +
"FROM " +
"	alarm_events a " + 
"		LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 " + 
"WHERE " +
"	a.eventtime " + 
"		BETWEEN '" + dateFormat({Alarm Analysis New.startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis New.endDate}, "yyyy-MM-dd HH:mm:ss") + "' " + 
"	AND a.eventtype = 0 " +
"GROUP BY " +
"	a.displaypath " +
"ORDER BY " +
"	duration DESC, a.displaypath ASC " +
"LIMIT " +
{Alarm Analysis New.Filters.Top.intValue}

Has anyone else had problems with the Alarm Analysis template? I believe my db is indexed correctly but I can’t imagine why this query is taking such a long time.

Cheers!

2

Do you have an index on the combination of eventid and eventtype?

3

I don’t believe so. Here are the results from show index from alarm_events

image.png973×55 3.93 KB

4

You definitely need that index for the join to run fast. You will be shocked at the difference.

1 Like
5

Thanks for the advice - What is the best way of creating the index between the two columns?

6

Hi pturmel,

This definitely solved the issue. Generating an index over the eventid and eventtype greatly sped up my queries.

Thanks for the help!

1 Like
7

How do I create that index in ms sql?
Can you offer any guidance?

8

Use the Create Index statement? Something like this, perhaps:

CREATE INDEX ix_alarm_events ON alarm_events (eventid, eventtype);