ETL9.1 - MSSQL Server - Performance issue when searching employees for Approve Payable Time (Doc ID 2123298.1)

Last updated on DECEMBER 14, 2016

Applies to:

PeopleSoft Enterprise HCM Time and Labor - Version 9.1 and later
Information in this document applies to any platform.

Symptoms

On : 9.1 version, Approvals, MSSQL Server database

ACTUAL BEHAVIOR
---------------
Click Get Employees on Approve Time and screen times out.
Approvals are setup by SupervisorId. When Get Employees is selected on Approve Payable Time, the Processing icon in the right corner displays. But it times out or stays in Processing and the employee list is not displayed after ten minutes or so. Only applicable during seasons of high system usage on MSSQL Server database. The issue seems to lie in the nested statements in the SQL.

 

Here's an example of the SQL that's taking a long time to execute:

SELECT DISTINCT J.EMPLID ,J.EMPL_RCD ,A.NAME ,J.BUSINESS_UNIT ,J.JOBCODE ,JC.DESCR ,J.DEPTID ,D.DESCR ,J.SUPERVISOR_ID ,J.REPORTS_TO ,J.LOCATION ,J.COMPANY ,J.PAYGROUP ,J.GP_PAYGROUP ,ED.WORKGROUP ,ED.TASKGROUP ,J.POSITION_NBR FROM PS_JOBCODE_TBL JC, PS_JOB J, PS_TL_EMPL_DATA ED, PS_TL_GROUP_DTL F, PS_TL_GRP_SECURITY S, PS_PERSON_NAME A, PS_DEPT_TBL D WHERE S.TL_GROUP_ID = F.TL_GROUP_ID AND F.EMPLID = J.EMPLID AND F.EMPL_RCD = J.EMPL_RCD AND F.EMPLID = A.EMPLID AND F.EMPLID = ED.EMPLID AND F.EMPL_RCD = ED.EMPL_RCD AND S.ROWSECCLASS = 'DPKNL3' AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.EFFDT <= '2015-10-13') AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT = J.EFFDT ) AND ED.EFFDT = ( SELECT MAX(ED1.EFFDT) FROM PS_TL_EMPL_DATA ED1 WHERE ED1.EMPLID = ED.EMPLID AND ED1.EMPL_RCD = ED.EMPL_RCD AND ED1.EFFDT <= '2015-10-13') AND JC.SETID = J.SETID_JOBCODE AND JC.JOBCODE = J.JOBCODE AND JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_TBL JC1 WHERE JC1.SETID = J.SETID_JOBCODE AND JC1.JOBCODE = J.JOBCODE AND JC1.EFFDT <= '2015-10-13') AND D.SETID = J.SETID_DEPT AND D.DEPTID = J.DEPTID AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_DEPT_TBL D1 WHERE D1.SETID = J.SETID_DEPT AND D1.DEPTID = J.DEPTID AND D1.EFFDT <= '2015-10-13') AND A.EMPLID <> '16313' AND EXISTS (SELECT 'X' FROM PS_TL_PAYABLE_TIME AP WHERE J.EMPLID=AP.EMPLID AND J.EMPL_RCD=AP.EMPL_RCD AND AP.DUR BETWEEN '2015-10-07' AND '2015-10-13' AND AP.PAYABLE_STATUS='NA' AND AP.SEQ_NBR IN (SELECT SEQ_NBR FROM PS_TL_APP_PAY_VW WHERE OPRID='MBROWN'))
 

EXPECTED BEHAVIOR
-----------------------
When the get employee button is clicked on the approval payable time page the results are expected to be displayed in few seconds.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Navigate to manager self service> time management> approve time and exceptions> approve payable time
2. click on get employees button the page times out.


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot process payable time approvals.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms