We experience very slow response on deleting rows from the dbo.service_alarm table of the offload DB. Our offload DB is an MSSQL DB.
We plan to delte data from this table when deleting old records from the dbo.alarm table by using the primary keys from dbo.alarm (Id and DmaId) By doing something like this (pseudo code):
SELECT Id, DmaId FROM alarm WHERE age is older than xx days
DELETE FROM service_alarm WHERE iAlarm = alarm.id AND iAlarmDMA = alarm.DmaId
Are there other ways this could/should be done?
As a reference it would be interesting to see how the cleanup job for the local MySQL DB's are done for this table.
In our active MySQL database we have 4 tables that are linked with the alarms
alarm: contains all alarms
Then we have 3 different tables linked to that alarm table:
alarm_property, service_alarm and interface alarm
We clear first every 15 min the alarm table with the conditions defined in:
System Center > System Settings > time to live
afterwards we do a query on the other tables and delete the entries where no reference exist anymore in the alarm table
This way you also don't need to keep a list of alarms you deleted and if something got stuck during the delete process it's not much of an issue since the next cycle it will try again.
Also as reference, the structure of all the tables can be found here:
The cleanup job on the local DB first cleans the alarm table and then just does:
DELETE FROM service_alarm WHERE (iAlarmDMA, iAlarm) NOT IN (SELECT dmaID, id FROM alarm);
If you only want to remove from service_alarm and not from alarm, you might be able to do it with:
DELETE FROM service_alarm WHERE (iAlarmDMA, iAlarm) IN (SELECT dmaID, id FROM alarm where toa < 'TIME');
Though the way you posted would also work and would result in smaller queries (though it might be a lot of queries)