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
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
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
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.
debug = 0
if (debug = 1) then
(
/* Log statements here. */
)