need help to optimize this query

[kundan sinha]

Hi,

I have this query running in application and I found out that this query was taking maximum time in execution. Can any body help me in optimizing this query.

Your suggestions are welcome


SELECT (A.FIRST_NAME||' ' ||A.LAST_NAME) NAME,

b.EXTERNAL_ID EMPLID ,

decode(d.descr,NULL,'', d.descr ) jobcodedescr,

decode(f.descr2,NULL,'', f.descr2 ) deptdescr,

decode(i.descr2,NULL,'', i.descr2 ) city

from

(
select descr2 ,fieldvalue FROM sp_code_descr_tbl y WHERE fieldname='LOCATION' and language_cd='ENG'
and EFFDT = (select max(effdt) from sp_code_descr_tbl where fieldname=y.fieldname and language_cd=y.LANGUAGE_CD
and fieldvalue =y.FIELDVALUE
and effdt <= trunc(sysdate))
) i,

(
select person_id,jobcode,deptid,location,setid_jobcode,setid_dept,setid_location from sp_job z where
person_id=':1:' and empl_rcd_num = (select max(empl_rcd_num) from sp_job where person_id = z.person_id)
and effdt = (select max(effdt) from sp_job where person_id = z.person_id)
and effseq = (select max(effseq) from sp_job where person_id = z.person_id)
) c ,

(
SELECT descr2 ,fieldvalue FROM sp_code_descr_tbl x where fieldname='DEPT' and language_cd=':2:'
and EFFDT =( select max(effdt) from sp_code_descr_tbl where fieldname=x.fieldname and language_cd=x.LANGUAGE_CD
and fieldvalue =x.FIELDVALUE
and effdt <= trunc(sysdate))
) f ,

(
select descr ,fieldvalue FROM sp_code_descr_tbl y WHERE fieldname='JOBCODE' and language_cd=':2:'
and EFFDT = (select max(effdt) from sp_code_descr_tbl where fieldname=y.fieldname and language_cd=y.LANGUAGE_CD
and fieldvalue =y.FIELDVALUE
and effdt <= trunc(sysdate))
) d ,

(
select external_id,person_id FROM sp_pers_trnsltn_t WHERE DATA_SRCE_CD = 'HRMS_GEMS' and person_id =':1:'
) b ,

sp_names a

WHERE
a.person_id =':1:'
and a.name_type = ':3:'
and a.effdt = (select max(effdt) from sp_names where person_id = a.person_id and name_type =a.name_type
and effdt <= trunc(sysdate))
AND c.person_id(+)= a.person_id
AND i.fieldvalue(+) = c.location
AND f.fieldvalue(+) = c.deptid
and d.fieldvalue(+) = c.jobcode
and b.person_id (+) = a.person_id

[Michael Matola]

That's an exceedingly PeopleSoft query. You might want to ask on a PeopleSoft-specific forum instead.

By the way, in the part where you're selecting against ps_job, this part of the subquery is probably wrong:

and effseq = (select max(effseq) from sp_job where person_id = z.person_id)

You probably want:

and effseq = (select max(effseq) from sp_job where person_id = z.person_id and effdt = z.effdt)

[Jeanne Boyarsky]

Kundan,
That looks like a very complex query. Things to look at:
1) Can the query be simplified or broken up into multiple smaller queries? Many nested selects are often slow.
2) Run your database's explain tool to see the execution plan. If it is doing any full table scans, see if you can add an index to help.