Performance Tuning Guide

10.5.6
- 10.5.8
- 10.5.7
- 10.5

Back Next

Expression Optimization

Some expressions used in a transformation might decrease performance.

Consider the following solutions for expression bottlenecks:

Isolate slow expressions.: Slow expressions slow mapping performance. To isolate slow expressions, remove expressions from the mapping one at a time, and run the mapping to determine the time it takes to run the mapping without the expression. If there is a significant difference in mapping run time, look for ways to optimize the slow expression.
Complete the following steps to evaluate expression performance:
Time the mapping with the original expressions.
Copy the mapping and replace half of the complex expressions with a constant.
Run and time the edited mapping.
Make another copy of the mapping and replace the other half of the complex expressions with a constant.
Run and time the edited mapping.
Factor 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.
Minimize aggregate function calls.: When writing expressions, factor out as many aggregate function calls as possible. Each time you use an aggregate function call, the Data Integration Service must search and group the data. For example, in the following expression, the Data Integration Service 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 below, the Data Integration Service adds COLUMN_A to COLUMN_B, then finds the sum of both.

SUM(COLUMN_A + COLUMN_B)
Replace 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 performance.
Choose numeric versus string operators.: The Data Integration Service processes numeric operations faster than string operations. For example, if you look up large amounts of data on two columns, EMPLOYEE_NAME and EMPLOYEE_ID, configuring the lookup around EMPLOYEE_ID improves performance.
Optimize CHAR-CHAR and CHAR-VARCHAR comparisons.: When the Data Integration Service performs comparisons between CHAR and VARCHAR columns, it slows each time it finds trailing blank spaces in the row. You can use the TreatCHARasCHARonRead option when you configure the Data Integration Service in the Informatica Administrator so that the Data Integration Service does not trim trailing spaces from the end of Char source fields.
Choose DECODE versus LOOKUP.: When you use a LOOKUP function, the Data Integration Service must look up a table in a database. When you use a DECODE function, you incorporate the lookup values into the expression so the Data Integration Service does not have to look up a separate table. Therefore, when you want to look up a small set of unchanging values, use DECODE to improve performance.
Use operators instead of functions.: The Data Integration Service reads 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
Optimize 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, ))))))))