• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

need help to optimize this query

 
kundan sinha
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
whippersnapper
Ranch Hand
Posts: 1817
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34198
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic