# Ignition 8 Alarm Journal Query speeds **URL:** \ **Category:** Ignition\ **Tags:** ignition80\ **Created:** [July 24, 2020, 4:08pm UTC](https://forum.inductiveautomation.com/t/ignition-8-alarm-journal-query-speeds/37059 "2020-07-24T16:08:53Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![ssilk76](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@ssilk76](https://forum.inductiveautomation.com/u/ssilk76)\ **Post date:** [July 24, 2020, 4:08pm UTC](https://forum.inductiveautomation.com/t/ignition-8-alarm-journal-query-speeds/37059/1 "2020-07-24T16:08:53Z")
We recently transitioned from 7.9.10 to 8.0.12 and overall it has been a fairly smooth transition. However… I have some alarm journal queries where I gather the top 10 alarms by duration and frequency for a machine in a given time and I graph them (I lifted the queries from the journaling example). On our old server running 7.9.10 the queries were not super fast but it would take 10 seconds or so to complete. On our new server these queries are showing in the performance monitor that they are taking 20 to 30 minutes. Has anyone else noticed similar issues in 8 or do I need to need to beat on my sql guys? code is shown below just for fun: SELECT TOP 10 a.displaypath, SUM((DATEDIFF(S, a.eventtime, COALESCE(c.eventtime, CURRENT\_TIMESTAMP)))/60.0) duration\_minutes FROM alarm\_events a LEFT JOIN alarm\_events c ON c.eventid = a.eventid AND c.eventtype = 1 WHERE a.eventtime BETWEEN ‘{Root Container.Date Range.startDate}’ AND ‘{Root Container.Date Range.endDate}’ AND a.eventtype = 0 AND (a.source like ‘prov:default:/tag:{Root Container.ASSET\_NUMBER}/ALARMS%’ – OR a.source like ‘prov:default:/tag:00608/ALARMS%’ ) GROUP BY a.displaypath, a.source ORDER BY duration\_minutes DESC ---
**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:** [July 25, 2020, 12:18am UTC](https://forum.inductiveautomation.com/t/ignition-8-alarm-journal-query-speeds/37059/2 "2020-07-25T00:18:13Z")
I’m gonna guess you are missing an index on `alarm_events.eventtime`. ---
**Author:** ![ssilk76](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@ssilk76](https://forum.inductiveautomation.com/u/ssilk76)\ **Post date:** [July 28, 2020, 3:21pm UTC](https://forum.inductiveautomation.com/t/ignition-8-alarm-journal-query-speeds/37059/3 "2020-07-28T15:21:54Z")
I do have an index in this table, or are you referring to just a single entry missing an index? ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/5/c5cac317400f24eaef491bce412016f8237a5655.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/6/f6816aad4be720e3a787d585239fc123985b32a9.png) ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [July 28, 2020, 4:44pm UTC](https://forum.inductiveautomation.com/t/ignition-8-alarm-journal-query-speeds/37059/4 "2020-07-28T16:44:24Z")
Since the heavy lifting is done via math on the eventtime column, you should add an index to that column. ---
**Author:** ![ssilk76](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@ssilk76](https://forum.inductiveautomation.com/u/ssilk76)\ **Post date:** [July 28, 2020, 4:54pm UTC](https://forum.inductiveautomation.com/t/ignition-8-alarm-journal-query-speeds/37059/5 "2020-07-28T16:54:26Z")
Since this is a table created by Ignition through alarm journaling, will there be any ill effect from adding an index column to the table? ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [July 28, 2020, 4:58pm UTC](https://forum.inductiveautomation.com/t/ignition-8-alarm-journal-query-speeds/37059/6 "2020-07-28T16:58:27Z")
Just to be clear, you are adding an index of the column, and not adding a column, usually using the database administration tool of your choice. No, there should not be any ill effects. ---
**Author:** ![ssilk76](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@ssilk76](https://forum.inductiveautomation.com/u/ssilk76)\ **Post date:** [July 28, 2020, 5:20pm UTC](https://forum.inductiveautomation.com/t/ignition-8-alarm-journal-query-speeds/37059/7 "2020-07-28T17:20:47Z")
Thanks for the input, I am SQL deficient, so I will now go about figuring out how I can test this somewhere where it won’t matter if(when) I screw it up. ---
**Author:** ![gregory.sachs](https://avatars.discourse-cdn.com/v4/letter/g/e47774/32.png) [@gregory.sachs](https://forum.inductiveautomation.com/u/gregory.sachs)\ **Post date:** [November 29, 2023, 4:04pm UTC](https://forum.inductiveautomation.com/t/ignition-8-alarm-journal-query-speeds/37059/8 "2023-11-29T16:04:36Z")
> [@ssilk76](#): > > SELECT > TOP 10 > a.displaypath, > SUM((DATEDIFF(S, a.eventtime, COALESCE(c.eventtime, CURRENT\_TIMESTAMP)))/60.0) duration\_minutes > FROM > alarm\_events a > LEFT JOIN alarm\_events c ON c.eventid = a.eventid AND c.eventtype = 1 > WHERE > a.eventtime > BETWEEN ‘{Root Container.Date Range.startDate}’ AND ‘{Root Container.Date Range.endDate}’ > AND > a.eventtype = 0 > AND > (a.source like ‘prov:default:/tag:{Root Container.ASSET\_NUMBER}/ALARMS%’ – OR a.source like ‘prov:default:/tag:00608/ALARMS%’ > ) > GROUP BY > a.displaypath, a.source > ORDER BY > duration\_minutes DESC Just wanted to say thanks for posting this query, it was useful for me!