Hello All,
We have a lookup that browse tasks list based on the project ID input. The same lookup is browsing data in 4-5 seconds in other environments but executing till 1 minute in production.
Also, we found that select statement have subquery on prassignment table and that is causing the slowness. When we remove that select part on prassignment, it executes much faster in production.
Not sure of the reason why prassignment table is fetching data slowly in production whereas works fast in other environments. There is not much difference in the number of records present in prassignment table for diff. environments.
When we executed ANALYSE table statement on prassignment to compute statistics, the query again executed fast in 2-3 seconds in production. But again after few hours, its slow (1 minute)
SELECT task.prid TaskID,
task.prexternalid ExternalID,
task.prname Task_name,
DECODE(task.prstatus,0,'Not Started',1,'Started',2,'Completed') Status,
phase.prname||'/'||task.prname task_hierarchy,
TRUNC(task.prstart) StartDate,
TRUNC(task.prfinish) EndDate,
(SELECT SUM(PRESTSUM)/3600 FROM prassignment WHERE prtaskid=task.prid) ETC, -- causing slowness
DECODE(odft.z_labor,1,'Yes',0,'No') Task_Labor,
odft.z_labor Task_Labor_f
FROM prtask task
INNER JOIN odf_ca_task odft ON odft.id=task.prid
LEFT OUTER JOIN PRTASK phase ON task.prwbssequence BETWEEN phase.prwbssequence AND phase.wbs_nnbseq - 1
AND task.prprojectid = phase.prprojectid
AND phase.prwbslevel = task.prwbslevel - 1
WHERE task.pristask = 1
AND task.prismilestone=0
and 1=
(case WHEN ODFT.Z_LABOR = 0 and NOT exists
(SELECT 1 FROM ODF_CA_Z_SUBS_ASSOCIATION SUBSIDYASS where task.prid = SUBSIDYASS.Z_SUBS_ASSC_TASK)
THEN 1
WHEN nvl(ODFT.Z_LABOR,2) <> 0 AND NOT EXISTS
(SELECT 1 FROM odf_ca_z_subs_association subsidyAss,
ODF_CA_Z_SUBSIDY_PROJECT SUBSIDY
WHERE subsidy.id = subsidyAss.odf_parent_id and task.prid = SUBSIDYASS.Z_SUBS_ASSC_TASK
AND SUBSIDY.ID = 5000001)
THEN 1
ELSE 0 END)
AND (:proj is null or :proj = task.prprojectid )
Please, help us so that lookup browse data more quickly as it is doing in lower environments.
Thanks,
Pragya Singh