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.
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)