[Automated suite cleaning | Perf] Optimize the count request
Affected version : 4.1.0 & following until 6.0.0
Expected behaviour
When the user use the "Automated suite cleaning" feature in the administration space, a count request is used :
select COUNT(distinct AUTOMATED_SUITE.SUITE_ID), COUNT(distinct AUTOMATED_EXECUTION_EXTENDER.EXTENDER_ID) from AUTOMATED_SUITE left outer join AUTOMATED_EXECUTION_EXTENDER on AUTOMATED_SUITE.SUITE_ID = AUTOMATED_EXECUTION_EXTENDER.SUITE_ID left outer join ITERATION_TEST_SUITE on ITERATION_TEST_SUITE.TEST_SUITE_ID = AUTOMATED_SUITE.TEST_SUITE_ID left outer join ITEM_TEST_PLAN_EXECUTION on AUTOMATED_EXECUTION_EXTENDER.MASTER_EXECUTION_ID = ITEM_TEST_PLAN_EXECUTION.EXECUTION_ID left outer join ITEM_TEST_PLAN_LIST on ITEM_TEST_PLAN_EXECUTION.ITEM_TEST_PLAN_ID = ITEM_TEST_PLAN_LIST.ITEM_TEST_PLAN_ID left outer join CAMPAIGN_ITERATION on (CAMPAIGN_ITERATION.ITERATION_ID = AUTOMATED_SUITE.ITERATION_ID or CAMPAIGN_ITERATION.ITERATION_ID = ITERATION_TEST_SUITE.ITERATION_ID or CAMPAIGN_ITERATION.ITERATION_ID = ITEM_TEST_PLAN_LIST.ITERATION_ID) left outer join CAMPAIGN_LIBRARY_NODE on CAMPAIGN_ITERATION.CAMPAIGN_ID = CAMPAIGN_LIBRARY_NODE.CLN_ID left outer join PROJECT on CAMPAIGN_LIBRARY_NODE.PROJECT_ID = PROJECT.PROJECT_ID where DATE_ADD(AUTOMATED_SUITE.CREATED_ON, interval PROJECT.AUTOMATED_SUITES_LIFETIME day) < ?
It should complete in a "reasonable" amount of time.
Observed behaviour :
Currently, the request takes several minutes to complete and should be optimized.