Ignition 8 Alarm Journal Query speeds

Ignition
1

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

2

I’m gonna guess you are missing an index on alarm_events.eventtime.

3

I do have an index in this table, or are you referring to just a single entry missing an index?

image

image
image1160×301 32.6 KB

4

Since the heavy lifting is done via math on the eventtime column, you should add an index to that column.

5

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?

6

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.

2 Likes
7

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.

8

Just wanted to say thanks for posting this query, it was useful for me!