Our live system has roughly 5000 model instances(in one of the possible states i.e., started, completed, failed, resubmitted etc) but the number of records within wmprocessassoc table is around 2 million.
We have a script which deletes the data within wmprocessassoc table, and it uses the WMPROCESS as a base to extract the instanceid and contextid. The script is given below(script given below is only for completed model instances):
create table TEMP_WMPROCESS as (select instanceid,contextid from WMPROCESS where status=2);
delete from WMPROCESSASSOC where contextid in (select contextid from TEMP_WMPROCESS);
Is there any other sql which needs to be run? We have not moved to the archive scripts provided by WM because of performance issues.
I also need to cleanup this table and wanted to know what is the role of this table. What will happen to the model instances if this table is truncated? Please send me your suggestions.