@PGriffith we are looking into taking advantage of DB side functionality to handle partitioning. The main problem we have with alarms is size control leading to issues such as those mentioned on this post. We are adverse to issuing delete statements because they cause blocking. Pruning issues a delete statement.
@MoisesZV here are a few things that may be helpful to you. Bear in mind these are all for MSSQL so it may be slightly different for your case, syntax wise
At first, you can see which indexes already exist with this statement:
Use Ignition_Data;
SELECT
TableName = t.name,
IndexName = ind.name,
IndexId = ind.index_id,
ColumnId = ic.index_column_id,
ColumnName = col.name
FROM
sys.indexes ind
INNER JOIN
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
WHERE
ind.is_primary_key = 0
AND ind.is_unique = 0
AND ind.is_unique_constraint = 0
AND t.is_ms_shipped = 0
AND t.name = 'alarm_events'
ORDER BY
t.name, ind.name, ind.index_id, ic.is_included_column, ic.key_ordinal;
On a table that is auto-created by Ignition, we can see that there are 3 indexes that are created by default on eventtime, eventid, and eventtype.
What we have observed helps with speed a little bit is to add indexes like follows noting that we start form index 4 since up to 3 already exists:
USE Ignition_Data;
CREATE NONCLUSTERED INDEX alarm_events_ix4 ON alarm_events (source);
CREATE NONCLUSTERED INDEX alarm_events_ix5 ON alarm_events (displaypath);
CREATE NONCLUSTERED INDEX alarm_events_ix6 ON alarm_events (priority);
CREATE NONCLUSTERED INDEX alarm_events_ix7 ON alarm_events (eventflags);
CREATE NONCLUSTERED INDEX alarm_events_ix8 ON alarm_events ([eventtime], [eventtype]);
CREATE NONCLUSTERED INDEX alarm_events_ix9 ON alarm_events ([eventid], [eventtype]);
For reference, the places we start to see issues with query speed (how long it takes to populate the Alarm Journal Table) is when we get to several hundred million rows.
In addition to indexing, which I think will help, you should also take the chance to see if what alarms you are recording are actually useful. We did this activity recently and found that the majority of alarms are just for conveyor full status and that was not useful to operations. So we turned it off and then the DB grows in size much slower.
Nick
