Fix slow Workflow Designer opening

The Workflow Designer tab opens slowly. In addition, the following Bot Task manipulations inside Workflow Designer are also slow:

Creating
Saving
Searching on the right panel
Updating

These symptoms, along with the analysis of the thread dumps gathered for the moment of the Workflow Designer tab opening, might indicate that such performance degradation is caused by a large number of Bot Configurations in the environment.

Resolution

Excessive Bot Configs can be due to improper development practices when new Bot Config versions are deployed frequently in the environment. In this case, when a new Business Process version is created, new Bot Config versions appear. At the same time, the old ones become obsolete since they are only used in the old run and cannot be executed once more.

A workaround is to find the obsolete configs that are no longer associated with any non-deleted runs and soft-delete them.

Perform the following steps against the MS SQL database:

Execute the following script to create a stored procedure.

create procedure ct.purge_old_obsolete_bot_configs_sp
as
begin

   set xact_abort on;
   set nocount on;

   declare
         @cm_rows int
       , @c_rows int
       , @mc_rows int

   select mc1.id
   into #bot_configs
   from ct.bot_config   mc1
   left join ct.campaign    c1 on c1.machineconfigid = mc1.id
   left join ct.campaignmap cm1 on cm1.campaign = c1.id
   left join ct.run         rr1 on rr1.campaign_id = cm1.parent
   where mc1.deleted = 0
   group by mc1.id
   having sum(case when rr1.status <> 'DELETED' then 1 else 0 end) = 0;
  
   begin transaction;

   update cm
   set cm.deleted  = 1
   from #machine_configs mcid
   join ct.campaign    c on c.machineconfigid = mcid.id
   join ct.campaignmap cm on cm.campaign = c.id;

   set @cm_rows = @@rowcount

   update c
   set c.status = 'DELETED'
   from #machine_configs mcid
   join ct.campaign    c on c.machineconfigid = mcid.id;
  
   set @c_rows = @@rowcount

   update mc
   set mc.deleted = 1
   from #machine_configs mcid
   join ct.machine_config mc on mc.id = mcid.id;
  
   set @mc_rows = @@rowcount

   commit;

   print formatmessage('Deleted in ct.campaignmap: %i row(s);', @cm_rows);
   print formatmessage('Deleted in ct.campaign: %i row(s);', @c_rows);
   print formatmessage('Deleted in ct.machine_config: %i row(s);', @mc_rows);

end;
go

Ensure that the WorkFusion service on the APP server is stopped.
```
wfmanager stop workfusion
```

Execute the following procedure:

exec ct.purge_old_obsolete_bot_configs_sp

If the procedure finishes without issues, you will get the statistics on affected rows.

Deleted in ct.campaignmap: 11 row(s);
Deleted in ct.campaign: 23 row(s);
Deleted in ct.machine_config: 83 row(s);

Start WorkFusion again.
```
wfmanager start workfusion
```

Resolution​

Resolution