Using Better Coding Techniques for Improved Performance

This section discusses how to:

  • Run a SQL trace.

  • Optimize SQL.

  • Use the GetNextNumberWithGaps function.

  • Consolidate PeopleCode programs.

  • Move PeopleCode to a component or page definition.

  • Send messages in the SavePostChange event.

  • Use metadata and the RowsetCache class.

  • Setting MaxCacheMemory

Run a SQLTrace and review the transaction for SQL statements that have a long processing time.

The duration column (Dur= ) in a SQL trace displays this information. If the duration is greater than 100 milliseconds, you may be able to make this SQL statement run faster. Work with your database administrator to tune the SQL.

A simple join optimizes SQL more effectively than issuing two related SQL statements separately.

However, if your transaction requires a complex SQL statement (for instance, one that uses correlated subqueries), consider breaking it up into multiple SQL statements. You may get more predictable performance this way.

Many applications use a sequence number as a unique key. The last number used is stored in a common table, and a SQL statement is issued to retrieve the last number used and update the table. This action locks the common table until the whole transaction is saved and the unit of work committed.

Instead, consider using the GetNextNumberWithGaps PeopleCode function whenever gaps in the sequence numbering are acceptable. The function retrieves the last number used, increments it by one, and updates the common table. This action is done in a separate unit of work to minimize the time a database lock is held on the common table.

GetNextNumberWithGaps issues a commit only when issued from the SavePreChange or Workflow event.

Consolidate RowInit PeopleCode into one field within the record to reduce the number of PeopleCode events that need to be triggered. Fewer PeopleCode programs results in fewer PeopleCode objects to manage. Do the same for RowInsert, SaveEdit, SavePreChange, SavePostChange, and Workflow PeopleCode programs.

Analyze transactions and move PeopleCode that is specific to a component from the record definition to the component or page definition. This action eliminates the need to execute conditional statements, such as If %Component = .

This action helps only if you are able to move all the PeopleCode in a program from the record to a component or page, and multiple components access that record.

Messages sent online should always be coded in the SavePostChange event. To minimize the time that PeopleTools maintains locks on single-threaded messaging tables, behind-the-scenes logic in the SavePostChange event defers sending the message until just before the commit for the transaction.

If your application uses data that is common, used by a number of users, and yet is fairly static, you may see a performance improvement by using the RowsetCache class.

PeopleTools stores application data in a database cache to increase system performance. The RowsetCache class enables you to access this memory structure, created at runtime, and shared by all users.

Note: Non-base language users may see different performance due to language table considerations.

See Understanding a Rowset Cache.

PeopleTools stores application data in a memory cache to increase system performance. However, too large a cache can leave insufficient available memory on your system, which leads to reduced performance.

Use this setting to specify the maximum size of the memory cache. PeopleTools prunes the cache to keep it within the specified size, and places the pruned data in a disk cache instead. Because using a disk cache can also reduce performance, the default setting might not be optimal for your application. You can adjust this setting to achieve the best trade-off between speed and available memory.

See Cache Settings.