Data Integration Performance Tuning > Optimizing mappings > Optimizing expressions
  

Optimizing expressions

You can optimize the expressions used in transformations. When possible, isolate slow expressions and simplify them.
Complete the following tasks to isolate the slow expressions:
  1. 1Remove the expressions one-by-one from the mapping.
  2. 2Run the mapping to determine the time it takes to run the mapping without the transformation. If there is a significant difference in mapping run time, look for ways to optimize the slow expression.

Factoring out common logic

If the mapping performs the same task in multiple places, reduce the number of times the mapping performs the task by moving the task earlier in the mapping.
For example, you have a mapping with five target tables. Each target requires a Social Security Number lookup. Instead of performing the lookup five times, place the Lookup transformation in the mapping before the data flow splits. Next, pass the lookup results to all five targets.

Minimizing aggregate function calls

When writing expressions, factor out as many aggregate function calls as possible.
Each time you use an aggregate function call, the mapping searches and groups the data. For example, in the following expression, Data Integration reads COLUMN_A, finds the sum, then reads COLUMN_B, finds the sum, and finally finds the sum of the two sums:
SUM(COLUMN_A) + SUM(COLUMN_B)
If you factor out the aggregate function call as shown below, Data Integration adds COLUMN_A to COLUMN_B, then finds the sum of both:
SUM(COLUMN_A + COLUMN_B)

Replacing common expressions with local variables

If you use the same expression multiple times in one transformation, you can make that expression a local variable.
You can use a local variable only within the transformation. However, by calculating the variable only once, you speed up performance.

Choosing numeric instead of string operations

Mappings process numeric operations faster than string operations.
For example, if you look up large amounts of data on two fields, EMPLOYEE_NAME and EMPLOYEE_ID, configuring the lookup around EMPLOYEE_ID improves performance.

Choosing DECODE instead of LOOKUP

When you use a LOOKUP function, the mapping looks up a table in a database. When you use a DECODE function, you incorporate the lookup values into the expression so the mapping doesn't have to look up a separate table. When you want to look up a small set of unchanging values, use DECODE to improve performance.

Using operators instead of functions

Mappings read expressions written with operators faster than expressions with functions. Where possible, use operators to write expressions.
For example, you have the following expression that contains nested CONCAT functions:
CONCAT( CONCAT( CUSTOMERS.FIRST_NAME, ‘ ’) CUSTOMERS.LAST_NAME)
You can rewrite that expression with the || operator as follows:
CUSTOMERS.FIRST_NAME || ‘ ’ || CUSTOMERS.LAST_NAME

Optimizing IIF functions

IIF functions can return a value and an action, which allows for more compact expressions.
For example, you have a source with three Y/N flags: FLG_A, FLG_B, FLG_C. You want to return values based on the values of each flag. You use the following expression:
IIF( FLG_A = 'Y' and FLG_B = 'Y' AND FLG_C = 'Y', VAL_A + VAL_B + VAL_C,
IIF( FLG_A = 'Y' and FLG_B = 'Y' AND FLG_C = 'N', VAL_A + VAL_B ,
IIF( FLG_A = 'Y' and FLG_B = 'N' AND FLG_C = 'Y', VAL_A + VAL_C,
IIF( FLG_A = 'Y' and FLG_B = 'N' AND FLG_C = 'N', VAL_A ,
IIF( FLG_A = 'N' and FLG_B = 'Y' AND FLG_C = 'Y', VAL_B + VAL_C,
IIF( FLG_A = 'N' and FLG_B = 'Y' AND FLG_C = 'N', VAL_B ,
IIF( FLG_A = 'N' and FLG_B = 'N' AND FLG_C = 'Y', VAL_C,
IIF( FLG_A = 'N' and FLG_B = 'N' AND FLG_C = 'N', 0.0,
)))))))
This expression requires 8 IIFs, 16 ANDs, and at least 24 comparisons.
If you take advantage of the IIF function, you can rewrite that expression as follows:
IIF(FLG_A='Y', VAL_A, 0.0)+ IIF(FLG_B='Y', VAL_B, 0.0)+ IIF(FLG_C='Y', VAL_C, 0.0)
This results in three IIFs, two comparisons, two additions, and a faster task run.

Optimizing window functions

Window functions process smaller frames faster. To reduce the size of the frame, decrease the number of rows between the frame offsets, and avoid including all preceding rows or all following rows.
Use a well-distributed partition key to create partitions of similar size.

Evaluating expressions

If you aren't sure which expressions slow performance, evaluate the expression performance to isolate the problem.
Complete the following steps to evaluate expression performance:
  1. 1Time the mapping run with the original expressions.
  2. 2Copy the mapping and replace half of the complex expressions with a constant.
  3. 3Run and time the edited mapping.
  4. 4Make another copy of the mapping and replace the other half of the complex expressions with a constant.
  5. 5Run and time the edited mapping.