Performance Tuning in Informatica

The goal of performance tuning is to optimize session performance by eliminating performance bottlenecks to get a better acceptable ETL load time.

Tuning starts with the identification of bottlenecks in the source, target, and mapping and further to session tuning. It might need further tuning on the system resources on which the Informatica PowerCenter Services are running.

We can use the test load option to run sessions when we tune session performance.

If we tune all the bottlenecks, we can further optimize session performance by increasing the number of pipeline partitions in the session.

Adding partitions will improve the performance by utilizing more of the system hardware while processing the session.

Determining the best way to improve performance can be complicated, so it's better to change one variable at a time. If the session performance does not improve, then we can return to the original configuration.

The goal of performance tuning is to optimize session performance so that the sessions run during the available load window for the Informatica Server.

We can increase the session performance with the help of the following tasks, such as:

• Network: The performance of the Informatica Server is related to network connections.
  Generally, the data moves across a network at less than 1 MB per second, whereas a local disk moves data five to twenty times faster. Thus network connections often affect session performance. So avoid network connections.
• Flat files: If the flat files are stored on a machine other than the Informatica server, then move those files to the device that consists of the Informatica server.
• Less Connection: Minimize the connections to sources, targets, and Informatica server to improve session performance. Moving the target database into the server system may improve session performance.
• Staging areas: If we use staging areas, then force the Informatica server to perform multiple data passes. Removing of staging areas can improve the session performance. Use the staging area only when it is mandatory.
• Informatica Servers: We can run the multiple Informatica servers against the same repository. Distributing the session load into the multiple Informatica servers improves the session performance.
• ASCII: Run the Informatica server in ASCII data movement mode improves the session performance. Because ASCII data movement mode stores a character value in one byte, Unicode mode takes 2 bytes to save a character.
• Source qualifier: If a session joins multiple source tables in one Source Qualifier, optimizing the query can improve performance. Also, single table select statements with an ORDER BY or GROUP BY clause can be beneficial from optimization, such as adding indexes.
• Drop constraints: If the target consists of key constraints and indexes, then it slows the loading of data. To improve the session performance, drop constraints and indexes before running the session (while loading facts and dimensions) and rebuild them after completion of the session.
• Parallel sessions: Running parallel sessions by using concurrent batches will also reduce the time of loading the data. So concurrent batches increase the session performance.
• Partitioning: The session improves the session performance by creating multiple connections for sources/targets and loads data in parallel pipelines.
• Incremental Aggregation: If a session contains an aggregator transformation, then we use incremental aggregation to improve session performance.
• Transformation Errors: Avoid transformation errors to improve session performance. Before saving the mapping, validate it and see if any error occurs, then transformation errors rectify it.
• Lookup Transformations: If the session contained lookup transformation, then we can improve the session performance by enabling the lookup cache. The cache enhances the speed by saving the previous data and hence no need to load that again.
• Filter Transformations: If the session contains filter transformation, create that filter transformation nearer to the sources, or we can use filter condition in source qualifier.
• Group transformations: Aggregator, Rank, and joiner transformation may often decrease the session performance because they must group data before processing it. We use sorted ports option to improve session performance, i.e., sort the data before applying the transformation.
• Packet size: We can improve the session performance by configuring the network packet size, which allows data to cross the network at one time. To do this, go to the server manager, choose server configure database connections.