Bottlenecks in Informatica

Bottleneck is the reason by which the performance of the Informatica ETL process gets slower. There are different types of Bottlenecks in Informatica. It can happen either while writing to the target or while reading from source and many more. Let’s discuss them in one by one.

Target Bottlenecks

Commonly we find target Bottlenecks in Informatica. This occurs when the Integration Service writes to a target database. It can be due to heavy load in the target or some other connection problems with the target.

Source Bottlenecks

Next is Source Bottlenecks. This occurs when the Integration Service reads from a source database. This can be due to the source query which is incorrect or source connection problems

Mapping Bottlenecks

When we do not have a source or target bottleneck, you may have a mapping bottleneck. Below are the steps to identify mapping bottlenecks

• Read the thread statistics and work time statistics in the session log. When the Integration Service spends more time on the transformation thread than the writer or reader threads, you have a transformation bottleneck.

• When the Integration Service spends more time on one transformation, it is the bottleneck in the transformation thread.

• Add a Filter transformation before each target definition. Set the filter condition to false so that no data is loaded into the target tables. If the time it takes to run the new session is the same as the original session, you have a mapping bottleneck.

Session Bottlenecks

If we don’t find any source, target, or mapping bottleneck, then we may have a session bottleneck. Small cache size, low buffer memory, and small commit intervals can cause session bottlenecks.

System Bottlenecks

Once we check for the source, target, mapping, and session bottlenecks then we need to tune the system to prevent system bottlenecks.

The Integration Service uses system resources to process transformations, run sessions, and read and write data. The Integration Service also uses system memory to create cache files for transformations, such as Aggregator, Joiner, Lookup, Sorter, XML, and Rank.