GeeCON Prague 2014*
The moose likes JDBC and the fly likes need help to optimize this  query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "need help to optimize this  query" Watch "need help to optimize this  query" New topic
Author

need help to optimize this query

kundan sinha
Greenhorn

Joined: Aug 01, 2005
Posts: 15
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

Joined: Mar 25, 2001
Posts: 1747
    
    2
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

Joined: May 26, 2003
Posts: 30580
    
154

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
GeeCON Prague 2014
 
subject: need help to optimize this query