SQR Optimisation Techniques

One of the biggest challenges I've faced while writing HRMS and Payroll reports has been the performance tuning of the SQRs. A typical payroll report which needs to display the EMPLID, NAME and Earnings for a certain pay period involves the join of three to four massive transaction tables and the execution time can exceed more than 15-20 minutes regularly. How do we bring it down to an acceptable run time of below 5 minutes? Though its a long deal getting such a drastic performance improvement, I have been able to get significant results by following the below mentioned steps:

1. Check your SQL queries: Most of the performance issue can be attributed to poorly written SQLs. Critically review each SQL statement of your SQR and ensure their correctness.

2. Usage of Temporary Tables: This is number two on my list. A strategy that I adopt when multiple tables have to be joined and the report involves complex processing, is to pull out the master data and insert the same into a temporary table that has been created in the begin-setup section of the SQR. Further processing can be done by referring the data in the temporary table and this reduces trips to transaction tables whereby giving us significant performance enhancements.

3. Usage of arrays: Arrays are excellent instruments to improve the maintainability of the program. I would always use arrays to hold values of totals and other variables that are to be re-initialised multiple times in the report (note that the values of an array can be reset by using the clear-array command). If variables were used instead, the number of variables that would have to be maintained, the initialisation routine etc. would add to the complexity to the program.

4. Dynamic SQLs: The usage of dynamic sqls greatly reduces the length of a program and helps in improving its performance. One requirement I had was to generate two reports with the same output fields and filter criteria, but one sorted by EMPLID and another by NAME. In this case, the only difference was the ORDER BY clause and instead of writing two different BEGIN-SELECTs, the requirement was achieved by maintaining a single BEGIN-SELECT and passing the order by clause as a dynamic SQL.

5. Break down your SQLs: This is something you will find in Peoplebooks also. Make sure that you are querying the database only when necessary. Let's say you have to produce a report which lists the EMPLID, NAME, BIRTHDATE, MANAGER'S NAME and JOBCODE DESCRIPTION. This will involve the join of 4 tables (including PS_NAMES aliased twice) and considering the key structure of these tables, will take considerable execution time. An alternate strategy would be to have a different procedure to get the JOBCODE descr where only the JOBCODE_TBL is queried for instead of making it a part of the join.

6. Minimal code and re-usable logic: The beauty of SQRs is that it is the most flexible tool in the Peoplesoft technical environment (especially considering the rigidity and ready made nature of an AE, CI or Peoplecode) and offers the developer multiple paths to one solution. Evolve a logic that re-uses your procedures and minimises database trips.

What about Load Lookups? Documentation says that Load Lookups give significant performance enhancements. Practically I have not been able to achieve this. The look up gets loaded at the start of the report and the time taken to load the lookup offsets any advantage that this concept delivers. Usage of temporary tables is a much better alternatives - in effect the concept of the two are inherently the same - reduce database trips and querying of large transaction tables.