Categories
HRMS Peoplesoft Performance SQL Tuning

HOLIDAY_SCHEDULE SQL

If you come across this as one of your top (if not THE top) SQLs in your HR system:

SELECT HOLIDAY_SCHEDULE, HOLIDAY, 
(CONVERT(CHAR(10),HOLIDAY,121)), DESCR 
 FROM PS_HOLIDAY_DATE 
WHERE HOLIDAY_SCHEDULE=@P1 
  AND HOLIDAY=@P2 
ORDER BY HOLIDAY_SCHEDULE, HOLIDAY

then look no further than the ABSW_WRK page which is a hidden page on most absence related components. There is a scroll showing the holiday dates. Unfortunately, some developer decided to put a related display in the grid, resulting in “n” executes of the above SQL – where “n” is the number of rows you have in PS_HOLIDAY_DATE for the given employees’s HOLIDAY_SCHEDULE.

In the case I came across there were some 200+ rows in the table and as a result after just 3 working days the above SQL had been executed 21 million times. This actually makes this more frequent than the PSVERSION check!

Personally, I’d remove the related display field from the scroll completely. It isn’t referenced nor visible so why take the overhead of 200+ SQL lookups?

Update: Further analysis revealed an issue with a cartesian product on a customized scroll based on the standard ABSW_WRK page. That was causing the majority of the 21 million lookups by filling the scroll with “n” times the 200+ rows in the holiday table. But my point is still very valid – why lookup something you never show the users? If you really need it – put it in the view used in the scroll so it comes “for free” with the population of the grid.