Slow Queries on Alarm Analysis Cloud Template

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 " +
"	a.displaypath " +
"	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.


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

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

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

1 Like

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

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

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

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

CREATE INDEX ix_alarm_events ON alarm_events (eventid, eventtype);