Improve Formula Performance

Use various approaches to ensure that your formulas are easy to read, use, understand, and processed efficiently.

Variable Names and Aliases

Use concise and meaningful variable names. If the names of database items are long, you can use aliases. The length of database item name doesn't impact the performance or memory usage of a formula.

Inputs Statements

Tip: The best approach is to use INPUTS statements rather than database items whenever possible. You don't have to access the database for input variables, which in turn speeds up your payroll run.

Here's an example of a formula without the INPUTS statement:

SALARY = SALARY_ANNUAL_SALARY / 12
RETURN SALARY

Here's how you can use the INPUTS statement efficiently:

INPUTS ARE ANNUAL_SALARY
SALARY = ANNUAL_SALARY / 12
RETURN SALARY

CHANGE_CONTEXTS Statement

Tip: The best approach is to use CHANGE_CONTEXTS statement only when required, because CHANGE_CONTEXTS can cause database item values to be fetched again from the database.

You can perform multiple context changes using a single CHANGE_CONTEXTS statement, instead of calling CHANGE_CONTEXTS from other CHANGE_CONTEXTS blocks.

For example, use a single CHANGE_CONTEXTS statement in the first formula, which is efficient than the second formula:

CHANGE_CONTEXTS(EFFECTIVE_DATE = l_eff_date, AREA1= 'California')
(
  /*Do something here*/
)

CHANGE_CONTEXTS(EFFECTIVE_DATE = l_eff_date)
(
CHANGE_CONTEXTS(AREA1 = 'California')
   (
    /*Do something here*/
   )
)

Don't use the CHANGE_CONTEXTS statement to set contexts that you would reasonably expect to be already set.

For example, the PERSON_ID context is generally already set in a formula that processes person information. If this context isn't set, then GET_CONTEXT doesn't fetch any value. GET_CONTEXT only returns the default value of -1. This code doesn't achieve anything:

l_person_id = GET_CONTEXT(PERSON_ID, -1)
l_effective_date = GET_CONTEXT(EFFECTIVE_DATE, '0001-01-01 00:00:00)(DATE))

CHANGE_CONTEXTS(PERSON_ID = l_person_id, EFFECTIVE_DATE = l_effective_date)
(
  /*Do something here*/
)

Database Items

Tip: The best approach is to retain only the required database items in a formula. Don't refer to database items in a formula unless it's absolutely required. Sometimes a formula might contain databases that it doesn't need, which causes the formula to make unnecessary database calls. As a result, the efficiency of the formula is impacted.

In this example, the use of database items always causes a database fetch for AGE.

S = SALARY
A = AGE
IF S < 20000 THEN
IF A < 20 THEN
  TRAINING_ALLOWANCE = 30
ELSE
  TRAINING_ALLOWANCE = 0

In this example, the use of database items fetches AGE only if salary is less than 20000:

IF SALARY < 20000 THEN
IF AGE < 20 THEN
  TRAINING_ALLOWANCE = 30
ELSE
  TRAINING_ALLOWANCE = 0

Database Items for HCM Extracts

Some database items are intended only for HCM Extracts reporting. These database items return a high amount of data and retrieve it inefficiently when used in a formula.

For example, an HCM Extracts database item retrieves information for all employees on the application, or complete information of a particular employee since the start of employment. This data retrieval is fine for reporting as reports process lots of data. Formulas usually process small amount of data limited to specific cases. HCM Extracts allow extra conditions to be added to limit the data returned, but formulas don't.

With these database items, more complex formulas are required to get the right data and the performance of the formulas is extremely poor because of the large data volumes. For example, the array database item PER_EXT_ALL_PHONE_AREA_CODE returns all phone area codes as of an effective date. Together with PER_EXT_ALL_PHONE_PERSON_ID, and PER_EXT_ALL_PHONE_TYPE, it's possible to get a person's home phone's area code.

But for a simpler formula and better performance, you can use the PER_PER_HOME_PHONE_AREA_CODE database item to retrieve the home phone's area code. PER_PER_HOME_PHONE_AREA_CODE uses PERSON_ID and EFFECTIVE_DATE contexts to get its data. PER_EXT_ALL_PHONE_AREA_CODE only uses the EFFECTIVE_DATE context.

While Loop

Use an EXIT statement or a change to the loop condition to leave a WHILE loop immediately once you complete your task. For example, don't continue when you have already found a single item that you were looking for.

Formula Logging

Formula logging can help during implementation when developing formulas. It can also help with problem diagnosis. When investigating problems logged through a support service request, Oracle may ask for logging information.

Although logging can be very useful, the logging process may impact performance and slow down processing in general. Other processes waiting on resources utilized by the logging process, may take longer to complete or be delayed in starting.

It is advisable to remove unnecessary logging statements once the purpose is served. If there is a need to retain logging statements for diagnostic purposes then it is suggested that they are executed conditionally, for example:

debug = 0 
if (debug = 1) then 
( 
   /* Log statements here. */ 
)

When logging is required, set debug = 1 and recompile the formula.

Note: Don't forget to disable logging when finished.