PeopleSoft SQL View Performance Tuning
edited Jan 27, 2014 2:39AM in PeopleTools and Lifecycle Management - PSFT (MOSC) 3 commentsAnswered ✓
Hi All,
I have a SQL Query which which contains approx 10 table join (Specially Inner & Left Outer) and finally this query produces View. Kindly note that this view not Created via PeopleSoft Application Designer, it is raw view created via backed. In Production Environment for selecting data this view takes approx 40 mins.Can anyone please help & guide how to optimize and tune this. Thanks in advance.
Note: i have tried with Optimizer hints but not gain any good performance.
SELECT
DISTINCT
A.EMPLID,
B.NAME_TITLE,
B.FIRST_NAME,
B.MIDDLE_NAME,
B.LAST_NAME,
CASE WHEN S.SEX='M' THEN 'Male' WHEN S.SEX='F' THEN 'Female' ELSE 'Unknown' END Gender,
I have a SQL Query which which contains approx 10 table join (Specially Inner & Left Outer) and finally this query produces View. Kindly note that this view not Created via PeopleSoft Application Designer, it is raw view created via backed. In Production Environment for selecting data this view takes approx 40 mins.Can anyone please help & guide how to optimize and tune this. Thanks in advance.
Note: i have tried with Optimizer hints but not gain any good performance.
SELECT
DISTINCT
A.EMPLID,
B.NAME_TITLE,
B.FIRST_NAME,
B.MIDDLE_NAME,
B.LAST_NAME,
CASE WHEN S.SEX='M' THEN 'Male' WHEN S.SEX='F' THEN 'Female' ELSE 'Unknown' END Gender,
0