Improve and Performance Tune your Application Engine Programs

Writing Efficient App Engine Programs

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 from 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)
• However, my tests suggest that this function does not cause any memory to be released by the process.

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.

Performance Considerations in Application Engine

It is always important to consider performance during design and development, but it is especially important when dealing with Application Engine programs. That is because AE programs often process a large number of transactions, and also because there are a number of fundamental design decisions that can have a dramatic affect on performance.

Performance tuning is often more of an art than a science, and there are exceptions to every rule. Still, the following general guidelines can be useful in achieving optimum performance.

Priority Considerations

These points should be on every designer’s and developer’s mind. They are given

priority because they can have a tremendous impact, and because it is much easier to implement them in the original design than it is to add them later.

• Set-Based Processing: Instead of loop constructs such as Do Select, Do When, Do While, or Do Until, consider using UPDATE or INSERT statements that will affect many rows at once.
• Temporary Tables: There are several reasons to consider using temporary tables in your program:

o The transaction tables may be too large to process directly against them with efficiency. A temporary table can hold an extract of the necessary data.

o The transaction tables may not have the correct index structure to process joins efficiently. A temporary table can be created with a more optimal index structure.

o The normalized data structure of the transaction tables may make it

difficult to access the required information. A temporary table can be

used to denormalize or “flatten” the data.

o It may not be possible to construct a single SQL statement to implement set-based processing. The temporary table can be used to store intermediate results.

o Although a single SQL statement might be possible, it may have such

complex joins that it performs badly. A temporary table again can be used to store intermediate results.

• Write efficient SQL: Set-based processing often leads to SQL statements with lots of joins. There are several techniques to keep in mind when writing or tuning such statements, so that they run quickly:

o Use as many indexes as possible in the join criteria. In particular, don’t

skip high-level keys.

o Avoid unnecessary joins. For example, when deriving SetID values from the business unit, use a separate select statement to find the SetID, and store it in the state record. Then it can be used as a bind variable in the main SQL statement.

o Avoid unnecessary subselects. For example, if all the rows selected from a table will have the same effective date, then use a separate select statement to find the effective date, and store it in the state record. Then it can be used as a bind variable in the main select.

o Join to the smallest table possible. For example, if effective-dated records exist in a parent-child relationship, then use the parent record for the effective date subselect.

o Use Oracle hints. This is appropriate when tuning a slow SQL statement. See the Oracle documentation for a complete list of hints, although two particularly useful ones are RULE (to force a statement to ignore statistics) and USE_INDEX (to force a statement to use a particular index). Generating the optimizer plan for the statement (see below) may help in selecting an appropriate hint.

Other Considerations

These points can be important in certain contexts, but they either do not carry as universal or dramatic an impact as the above points, or they are easier to implement by “tweaking” an existing program.

• SQL in PeopleCode: When possible, avoid using SQL in PeopleCode. It carries less overhead to use SQL actions instead.

• Mathematical Calculations in PeopleCode: When possible, avoid using SQL to perform mathematical calculations. For example, it is more efficient to use PeopleCode to increment a counter. The exception to this is if the same calculation must be performed on many rows of a table – in that case, use setbased processing.

• Commits: When and where commits are done should primarily be driven by restart logic. However, if doing commits within loop structures, it is a good idea to specify the commit frequency, so that commits may be done in sizable “chunks”.

• ReUse Statement: For SQL actions in loop structures, turn on the “ReUse

Statement” option. The exception to this is if dynamic SQL is being used.

• Bulk Insert: For INSERT statements in loop structures, use the Bulk Insert option. Note that this will not help if commits are done too frequently in the loop.

• %UpdateStats: After inserting a large amount of data into a temporary table, use the %UpdateStats command. This will help the Oracle optimizer process joins more intelligently. Note that this will only work if commits are enabled.
• Tracing: To identify where a performance problem is, run a trace of the

program, using the timing options. There are two important points to consider:

o When trying to pinpoint the problem turn off all tracing options other than the timing options. The overhead involved in tracing every statement can skew your results.

o If the problem has been narrowed down to a particularly complex SQL, it can help to generate the optimizer plan for that statement, using the DB Optimizer tracing option.