Monday, April 25, 2011

Insignificant Payroll Run Results

Oracle Payroll process posts lot of data to database for each payroll run. This data grows exponentially as number of employees & number of payroll runs grow. Generally pay_run_results and pay_run_result_values are the tables which take most of the data during payroll run. As these tables grow, they start to cause performance degradation in processes/queries involving these tables. Oracle provides couple of different options to keep number of rows to minimum in these tables:





  1. Use "Suppress insignificant indirect results " pay action parameter to not create any zero or null indirect results. Oracle also provides a patch "5744549" to purge existing null or zero indirect results. I have seen around 17% savings in pay_run_results table and 26% savings in payr_run_result_values table after implementing this solution.


  2. You can also enable sparse matrix for specific legislation to not create some null indirect or direct run results. Oracle also provides a process to purge existing data eligible to to be purged by sparse matrix option. This process mainly supresses null data rows. I have seen additional 26% savings in pay_run_result_values table


Overall implementing one or both options will reduce payroll run results and slows down the growth of the tables over the time. By implementing both solutions, i have seen 17% savings in pay_run_results table & 45% savings in pay_run_result_values table.