AnsweredAssumed Answered

Poorly Performing Query

Question asked by Robert Ensinger on Jun 10, 2016
Latest reply on Aug 29, 2016 by varve03

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.

 

poor query response.jpg

 

 

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.

Outcomes