[Performance]Slowness in the Campaigns workspace with big execution plans

The following request, launched through the URL : <squash>/squash/campaign-workspace/iteration/XXXXXX/test-plan?anchor=plan-exec is an example of octopus :

select test_suite.id, test_suite.uuid, test_suite.name, test_suite.description, test_suite.execution_status, test_suite.attachment_list_id, test_suite.created_by, test_suite.created_on, test_suite.last_modified_by, test_suite.last_modified_on, campaign_library_node.project_id, count(alias_36978044.tcln_id) as has_automated_tests, count(iteration_test_plan_item.dataset_id) as has_datasets from test_suite left outer join test_suite_test_plan_item on test_suite_test_plan_item.suite_id = test_suite.id left outer join iteration_test_plan_item on iteration_test_plan_item.item_test_plan_id = test_suite_test_plan_item.tpi_id join iteration_test_suite on test_suite.id = iteration_test_suite.test_suite_id join iteration on iteration_test_suite.iteration_id = iteration.iteration_id join campaign_iteration on iteration.iteration_id = campaign_iteration.iteration_id join campaign_library_node on campaign_iteration.campaign_id = campaign_library_node.cln_id left outer join (select test_suite.id, test_case.tcln_id from test_suite join test_suite_test_plan_item on test_suite_test_plan_item.suite_id = test_suite.id join iteration_test_plan_item on iteration_test_plan_item.item_test_plan_id = test_suite_test_plan_item.tpi_id join test_case on test_case.tcln_id = iteration_test_plan_item.tcln_id join test_case_library_node on test_case_library_node.tcln_id = test_case.tcln_id left outer join automation_request on automation_request.test_case_id = test_case.tcln_id join project on project.project_id = test_case_library_node.project_id join test_automation_server on test_automation_server.server_id = project.ta_server_id where (((project.automation_workflow_type <> 'NONE' and test_case.automatable = 'Y' and automation_request.request_status = 'AUTOMATED') or project.automation_workflow_type = 'NONE') and ((test_automation_server.kind = 'jenkins' and test_case.ta_test is not null) or (test_automation_server.kind = 'squashOrchestrator' and test_case.automated_test_technology is not null and test_case.automated_test_reference is not null and test_case.scm_repository_id is not null)))) as alias_36978044 on alias_36978044.id = test_suite.id
 where test_suite.id = 10001 group by test_suite.id, campaign_library_node.project_id;

This request might take over a minute on big test plans and numerous automatisations.

Replace it by:

select a.id,
       a.uuid,
       a.name,
       a.description,
       a.execution_status,
       a.attachment_list_id,
       a.created_by,
       a.created_on,
       a.last_modified_by,
       a.last_modified_on,
       d.project_id,
       (count(k.server_id) > 0) as has_automated_tests,
       (count(f.dataset_id) > 0) as has_datasets
  from test_suite a join iteration_test_suite b on a.id = b.test_suite_id
                    join campaign_iteration c on b.iteration_id = c.iteration_id
                    join campaign_library_node d on c.campaign_id = d.cln_id
               left join test_suite_test_plan_item e on a.id=e.suite_id
               left join iteration_test_plan_item f on e.tpi_id = f.item_test_plan_id
               left join test_case g on f.tcln_id = g.tcln_id
               left join automation_request h on g.tcln_id=h.test_case_id
               left join test_case_library_node i on g.tcln_id = i.tcln_id
               left join project j on i.project_id = j.project_id
               left join test_automation_server k on j.ta_server_id = k.server_id
                                                 and ( j.automation_workflow_type <> 'NONE' and g.automatable = 'Y' and h.request_status = 'AUTOMATED'
                                                    or j.automation_workflow_type = 'NONE')
                                                 and ( k.kind = 'jenkins' and g.ta_test is not null
                                                    or k.kind = 'squashOrchestrator' and g.automated_test_technology is not null and
                                                       g.automated_test_reference is not null and g.scm_repository_id is not null)
 where a.id = XXXXXX
group by a.id, d.project_id;

and the result will be instant.