Writing Efficient App Engine Program

Few high-level guidelines to be followed:

a) Apply Set Processing wherever it can be applied:

• Use Group by, Having, Exists clauses effectively
• Take extra care while writing sub queries and complex joins
• Don't forget to join PROCESS_INSTANCE, in case you've made it a key
• Fine tune the SQL ( Refer: Oracle Documentation)

b) Use Temp Table

• It improves AE performance significantly- Best Suited for Set Based Processing
• Facilitates parallel processing
• Custom Indexes can be created to achieve faster result
• Practice to make PROCESS_INSTANCE a key and employ the %Table meta-SQL
• Automatic data cleanse
• Dedicated Temp Table is preferred

c) Use Meta-SQL

• Make a habit of using Meta-SQL like %Join, %CurrentDateIn, %Table. It gives program more flexibility.
• It makes AE program more robust and platform independent and improves performance.
• Be aware of the limitation of these Meta-SQL, e.g %EffdtCheck doesn't work well with PS_JOB table
• %TruncateTable is faster than bulk delete statement. In case of Temp table use %TruncateTable(%Table(XXX_TAO))

d) Drop/Rebuild Indexes:

• If you're planning for massive Insert, you may wish to drop indexes and triggers from the table first and recreate them once the insert is done.
• It makes AE processing much faster. Just be careful that dropping indexes makes it prone to Duplicate rows. (You may leave primary key untouched).

e) Refrain using PeopleCode

• If the goal can be achieved using SQL, do not use PeopleCode.

f) Setting Commits

• It's recommended to use frequent and early Commit in case of Set based processing. It reduces load from Database server and enhance performance.

g) Reuse Statement

• Valid Only for SQL actions
• By dedicating a persistent cursor to that statement we can reuse the SQL Statement.
• When we select the ReUse property for a SQL action, %BIND fields is converted into real bind variables (like :1, :2, etc). This enables PeopleSoft Application Engine to compile the statement only once and dedicate a cursor, and re-execute it with new data multiple times. This reduction in compile time can result in dramatic improvements to performance.

h) Use %UpdateStats

• For better performance, refresh statistics on temp tables using %UpdateStats(record name ,[HIGH/LOW]) after each bulk load of data.

i) Use CollectGarbage()

• Specific to AE using App Classes.
• Use the CollectGarbage function to remove any unreachable application objects created by the Application Classes and hence release the memory. ( Also Refer: Unix Process Limit for PeopleSoft)
  

j) Use SQL Hints ( Tuning SQL)

• SQL Hints e.g. /* +APPEND */,/* +FIRST_ROWS(n) */, provides a mechanism which instruct the CBO to choose a certain query execution plan based on the specific criteria. ( Refer: Oracle Documentation)
  

k) Streaming

• Streaming is a process in which a large chunk of data is divided into multiple streams and all the stream processes in parallel.

l) Use Trace File

• Use trace file generated by Trace/TOOLSTRACESQL/TOOLSTRACEPC advisably. Find out areas where process is taking longer processing-time or where performance can be improved.