# Slow Queries on Alarm Analysis Cloud Template

**URL:** <https://forum.inductiveautomation.com/t/slow-queries-on-alarm-analysis-cloud-template/20290>\
**Category:** Ignition\
**Tags:** ignition79\
**Created:** [October 22, 2018, 12:32am UTC](https://forum.inductiveautomation.com/t/slow-queries-on-alarm-analysis-cloud-template/20290 "2018-10-22T00:32:20Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![jameswilliampain](https://avatars.discourse-cdn.com/v4/letter/j/3da27b/32.png) [@jameswilliampain](https://forum.inductiveautomation.com/u/jameswilliampain)\
**Post date:** [October 22, 2018, 12:32am UTC](https://forum.inductiveautomation.com/t/slow-queries-on-alarm-analysis-cloud-template/20290/1 "2018-10-22T00:32:20Z")

</div>

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:

```auto

"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!

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 22, 2018, 1:05am UTC](https://forum.inductiveautomation.com/t/slow-queries-on-alarm-analysis-cloud-template/20290/2 "2018-10-22T01:05:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jameswilliampain](https://avatars.discourse-cdn.com/v4/letter/j/3da27b/32.png) [@jameswilliampain](https://forum.inductiveautomation.com/u/jameswilliampain)\
**Post date:** [October 22, 2018, 1:21am UTC](https://forum.inductiveautomation.com/t/slow-queries-on-alarm-analysis-cloud-template/20290/3 "2018-10-22T01:21:19Z")

</div>

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

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/4/4a29266d22312db14d7127109e7ab74375a21db9.png)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 22, 2018, 1:23am UTC](https://forum.inductiveautomation.com/t/slow-queries-on-alarm-analysis-cloud-template/20290/4 "2018-10-22T01:23:56Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jameswilliampain](https://avatars.discourse-cdn.com/v4/letter/j/3da27b/32.png) [@jameswilliampain](https://forum.inductiveautomation.com/u/jameswilliampain)\
**Post date:** [October 22, 2018, 1:31am UTC](https://forum.inductiveautomation.com/t/slow-queries-on-alarm-analysis-cloud-template/20290/5 "2018-10-22T01:31:38Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jameswilliampain](https://avatars.discourse-cdn.com/v4/letter/j/3da27b/32.png) [@jameswilliampain](https://forum.inductiveautomation.com/u/jameswilliampain)\
**Post date:** [October 22, 2018, 3:02am UTC](https://forum.inductiveautomation.com/t/slow-queries-on-alarm-analysis-cloud-template/20290/6 "2018-10-22T03:02:46Z")

</div>

Hi pturmel,

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

Thanks for the help!

---

<div class="post-metadata">

**Author:** ![SCondo](https://avatars.discourse-cdn.com/v4/letter/s/e8c25b/32.png) [@SCondo](https://forum.inductiveautomation.com/u/SCondo)\
**Post date:** [February 7, 2019, 6:58pm UTC](https://forum.inductiveautomation.com/t/slow-queries-on-alarm-analysis-cloud-template/20290/7 "2019-02-07T18:58:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 7, 2019, 10:07pm UTC](https://forum.inductiveautomation.com/t/slow-queries-on-alarm-analysis-cloud-template/20290/8 "2019-02-07T22:07:19Z")

</div>

Use the [Create Index](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2017&viewFallbackFrom=sql-server-2014) statement? Something like this, perhaps:

```auto
CREATE INDEX ix_alarm_events ON alarm_events (eventid, eventtype);

```