Improving Performance in Informatica

1. Aggregator Transformation

You can use the following guidelines to optimize the performance of an Aggregator transformation.

a) Use Sorted Input to decrease the use of aggregate caches:

The Sorted Input option reduces the amount of data cached during the session and improves session performance. Use this option with the Source Qualifier Number of Sorted Ports option to pass sorted data to the Aggregator transformation.

b) Limit connected input/output or output ports:

Limit the number of connected input/output or output ports to reduce the amount of data the Aggregator transformation stores in the data cache.

c) Filter before aggregating:

If you use a Filter transformation in the mapping, place the transformation before the Aggregator transformation to reduce unnecessary aggregation.

2. Filter Transformation

The following tips can help filter performance:

a) Use the Filter transformation early in the mapping:

To maximize session performance, keep the Filter transformation as close as possible to the sources in the mapping. Rather than passing rows that you plan to discard through the mapping, you can filter out unwanted data early in the flow of data from sources to targets.

b) Use the Source Qualifier to filter:

The Source Qualifier transformation provides an alternate way to filter rows. Rather than filtering rows from within a mapping, the Source Qualifier transformation filters rows when read from a source. The main difference is that the source qualifier limits the row set extracted from a source, while the Filter transformation limits the row set sent to a target. Since a source qualifier reduces the number of rows used throughout the mapping, it provides better performance.

However, the source qualifier only lets you filter rows from relational sources, while the Filter transformation filters rows from any type of source. Also, note that since it runs in the database, you must make sure that the source qualifier filter condition only uses standard SQL. The Filter transformation can define a condition using any statement or transformation function that returns either a TRUE or FALSE value.

3. Joiner Transformation

The following tips can help improve session performance:

a) Perform joins in a database:

Performing a join in a database is faster than performing a join in the session. In some cases, this is not possible, such as joining tables from two different databases or flat file systems. If you want to perform a join in a database, you can use the following options:

·         Create a pre-session stored procedure to join the tables in a database before running the mapping.

·         Use the Source Qualifier transformation to perform the join.

b) Designate as the master source the source with the smaller number of records:

For optimal performance and disk storage, designate the master source as the source with the lower number of rows. With a smaller master source, the data cache is smaller, and the search time is shorter.

4. LookUp Transformation

Use the following tips when you configure the Lookup transformation:

a) Add an index to the columns used in a lookup condition:

If you have privileges to modify the database containing a lookup table, you can improve performance for both cached and uncached lookups. This is important for very large lookup tables. Since the Informatica Server needs to query, sort, and compare values in these columns, the index needs to include every column used in a lookup condition.

b) Place conditions with an equality operator (=) first:

If a Lookup transformation specifies several conditions, you can improve lookup performance by placing all the conditions that use the equality operator first in the list of conditions that appear under the Condition tab.

c) Cache small lookup tables:

Improve session performance by caching small lookup tables. The result of the Lookup query and processing is the same, regardless of whether you cache the lookup table or not.

d) Join tables in the database:

If the lookup table is on the same database as the source table in your mapping and caching is not feasible, join the tables in the source database rather than using a Lookup transformation.

e) Un Select the cache look-up option in Look Up transformation if there is no look up over-ride. This improves performance of session.