You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Performance Tuning of View PERS_SRCh_GBL in HRMS 9.1

1 reply [Last post]
Guest's picture
Guest

Hi Team, my Company holds data of more than 10 lacks and when I am trying to use PERS_SRCH_GBL as prompt, the system gets crashed, is it possible to tune View PERS_SRCH_GBL of HRMS 9.1.
Attaching the View

SELECT DISTINCT OPR.OPRID
, OPR.ROWSECCLASS
, SEC.EMPLID
,SEC.EMPL_RCD
, NM.NAME_DISPLAY
, NM.NAME_DISPLAY_SRCH
, NM.NAME
, NM.LAST_NAME_SRCH
, NM.SECOND_LAST_SRCH
, NM.FIRST_NAME
, NM.LAST_NAME
, NM.SECOND_LAST_NAME
, NM.NAME_AC
, NM.MIDDLE_NAME
, NM.NAME_DISPLAY
, SEC.PER_ORG
, SEC.POI_TYPE
, SEC.BUSINESS_UNIT
, SEC.SETID_DEPT
, SEC.DEPTID
, SEC.SETID_LOCATION
, SEC.LOCATION
, SEC.COMPANY
, SEC.REG_REGION
, SEC.INSTITUTION
, SEC.NATIONAL_ID
, SEC.HR_STATUS
, SEC.MILITARY_SERVICE
, SEC.MIL_RANK
, SEC.MIL_WORN_RANK
, SEC.APPT_TYPE
,SEC.EMPL_STATUS
,SEC.OTHER_ID_JPN
,SEC.FUTURE_FLG
,SEC.REPORTS_TO
,SEC.SUPERVISOR_ID
,SEC.SETID_JOBCODE
,SEC.JOBCODE
FROM PS_SJT_PERSON SEC
, PS_NAMES NM
, PSOPRDEFN OPR
WHERE SEC.APPT_TYPE <> '1'
AND NM.EMPLID = SEC.EMPLID
AND NM.NAME_TYPE = 'PRI'
AND NM.EFFDT = (
SELECT MAX(NM1.EFFDT)
FROM PS_NAMES NM1
WHERE NM1.EMPLID = NM.EMPLID
AND NM1.NAME_TYPE = 'PRI'
AND NM1.EFFDT <= CURRENT DATE )
AND (EXISTS (
SELECT 'X'
FROM PS_SJT_CLASS_ALL CLS
, PS_SJT_OPR_CLS SOC
WHERE CLS.SCRTY_SET_CD = 'PPLJOB'
AND CLS.SCRTY_TYPE_CD = SEC.SCRTY_TYPE_CD
AND CLS.SCRTY_KEY1 = SEC.SCRTY_KEY1
AND CLS.SCRTY_KEY2 = SEC.SCRTY_KEY2
AND CLS.SCRTY_KEY3 = SEC.SCRTY_KEY3
AND CLS.TREE = 'Y'
AND SOC.OPRID=OPR.OPRID
AND SOC.CLASSID = CLS.CLASSID
AND SOC.CLASSID = OPR.ROWSECCLASS
AND SOC.SEC_RSC_FLG = '1' )
OR EXISTS (
SELECT 'X'
FROM PS_SJT_CLASS_ALL CLS
, PS_SJT_OPR_CLS SOC
WHERE CLS.SCRTY_SET_CD = 'PPLJOB'
AND CLS.SCRTY_TYPE_CD = SEC.SCRTY_TYPE_CD
AND CLS.SCRTY_KEY1 = SEC.SCRTY_KEY1
AND CLS.SCRTY_KEY2 = SEC.SCRTY_KEY2
AND CLS.SCRTY_KEY3 = SEC.SCRTY_KEY3
AND CLS.TREE = 'N'
AND SOC.OPRID=OPR.OPRID
AND SOC.CLASSID = CLS.CLASSID )
OR EXISTS (
SELECT 'X'
FROM PS_SJT_CLASS_ALL CLS
, PS_SJT_OPR_CLS SOC
WHERE CLS.SCRTY_SET_CD = 'PPLJOB'
AND CLS.SCRTY_TYPE_CD = SEC.SCRTY_TYPE_CD
AND CLS.SCRTY_KEY1 = SEC.SCRTY_KEY1
AND CLS.SCRTY_KEY2 = SEC.SCRTY_KEY2
AND CLS.SCRTY_KEY3 = SEC.SCRTY_KEY3
AND CLS.TREE = 'Y'
AND SOC.OPRID=OPR.OPRID
AND SOC.CLASSID = CLS.CLASSID
AND SOC.CLASSID = OPR.ROWSECCLASS
AND SOC.SEC_RSC_FLG = '3' ))

Cheers!
Shyam

Saiyed 's picture
Saiyed (not verified)
Re: Performance Tuning of View PERS_SRCh_GBL in HRMS 9.1

One can easily tell the issue is so many EXISTS condition ...
I will suggest few things ...
1.Check indexes are proper on the source tables.
2.Runstats and reorgs on the source tables .
3. Check if you can get rid of the exists condition.
4. Create temp table on fly and split your query to isolate the exist condition.
(WITH Clause ).
5.check with the DBAs