Retrieval time of filtered Alarm & Event list is very slow when looking at IM events
When a query is done to get all events for a particular object, a filter on the object name column must be added. This is called a "filtered" query. Depending on how the event list on the IM (the IMMSGLOG) is configured, this query can take an extremely long time (approaching an hour in some cases) to return the the desired events.Symptoms:
When a Filter is entered on the Alarm & Event List Aspect, each of the entries in the IMMSGLOG (IM's Event List) must be queried to see if there is a match. If the IMMSGLOG is configured for 12 million entries and has near that many, then each of the 12 Million events must be checked. This checking is done in the Alarm & Event List Aspect itself (not Oracle) and is very time consuming. Resolution:
The only way to improve the performance of a "filtered" Alarm & Event List Aspect query retrieving events from a large IMMSGLOG on an IM is to add date ranges to the query. If you have two years of events in the IMMSGLOG, you may only be concerned about events in the last month or week. By adding a month or week time range to the query, the number of events that meet the criteria will be dramatically reduced. This is critical to improving performance as the messages that don't meet the time criteria are filtered out in Oracle on the IM and never returned to the Alarm & Event List Aspect.
Time ranges can be added add-hoc on the Alarm & Event List Aspect by selecting the Event Time Column. Or, preferably, by adding preconfigured time ranges to the Alarm & Event List Configuration Aspect using Runtime Filters. Then the operator can just select the preferred time range and there is no chance that an invalid time range could be entered (which would cause a long query). Here are a few figures showing the configuration and use of a pre-configured time range.
Note: the 800xA System version must be at SV5.0 SP2 Rev C or higher for time range filters (either Runtime Filter or add-hoc) on the Alarm & Event List Aspect to have any effect on query speeds. Older versions must hardcode a time range on the Alarm & Event List Configuration Aspect's Filter tab to improve query times.
Fig 1. Setting up the time range as a Runtime Filter on the Alarm & Event List Configuration Aspect
Fig 2. Selecting the time range on the Alarm & Event List Aspect