Occasionally, for reasons we've not yet been able to isolate, a query that runs in the CA PPM plumbing when the user either clicks on or returns to the Projects list 'hangs' the application as the SQL takes over 20 seconds to execute.
select * from (select row_number() over ( order by investment_name COLLATE Latin1_General_CI_AS_KS asc) row_num , count(*) over () num_rows , q.* from (SELECT i.id investment_int_id, i.code investment_code, i.code UNIQUE_CODE, i.name investment_name FROM inv_investments i, inv_projects p WHERE i.id = p.prid AND p.is_template = 1 AND (i.purge_flag = 0 OR i.purge_flag IS NULL) AND p.prid in (select object_instance_id from odfsec_project_v2 where user_id = 5002092) AND 1=1 and 1=1 and 2 = 2 AND i.is_active = 1 ) q) q where q.row_num < 50001 order by q.row_num
The issue comes then it goes and it's just this query that is impacted (it goes from taking milliseconds to over 20 seconds).
What could cause something like this? Any help with troubleshooting steps or experience chasing something like this is greatly appreciated.