Performance tuning in Informatica

The aim of performance tuning is optimize session performance so sessions run during the available load window for the Informatica Server. Increase the session performance by following.  The performance of the Informatica Server is related to network connections. Data generally 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 on session performance. So avoid   work connections.

1. Cache lookups if source table is under 500,000 rows and DON’T cache for tables over 500,000 rows. 
2. Reduce the number of transformations. Don’t use an Expression Transformation to collect fields. Don’t use an Update Transformation if only inserting. Insert mode is the default.
3. If a value is used in multiple ports, calculate the value once (in a variable) and reuse the result instead of recalculating it for multiple ports.
4. Reuse objects where possible.
5. Delete unused ports particularly in the Source Qualifier and Lookups.
6. Use Operators in expressions over the use of functions.
7. Avoid using Stored Procedures, and call them only once during the mapping if possible.
8. Remember to turn off Verbose logging after you have finished debugging.
9. Use default values where possible instead of using IIF (ISNULL(X),,) in Expression port.
10. When overriding the Lookup SQL, always ensure to put a valid Order By statement in the SQL. This will cause the database to perform the order rather than Informatica Server while building the Cache.
11. Improve session performance by using sorted data with the Joiner transformation. When the Joiner transformation is configured to use sorted data, the Informatica Server improves performance by minimizing disk input and output.
12. Improve session performance by using sorted input with the Aggregator Transformation since it reduces the amount of data cached during the session. 
13. Improve session performance by using limited number of connected input/output or output ports to reduce the amount of data the Aggregator transformation stores in the data cache.
14.  Use a Filter transformation prior to Aggregator transformation to reduce unnecessary aggregation.
15.  Performing a join in a database is faster than performing join in the session. Also use the Source Qualifier to perform the join.
16. Define the source with less number of rows and master source in Joiner Transformations, since this reduces the search time and also the cache.
17. When using multiple conditions in a lookup conditions, specify the conditions with the equality operator first.
18. Improve session performance by caching small lookup tables.
19. If the lookup table is on the same database as the source table, instead of using a Lookup transformation, join the tables in the Source Qualifier Transformation itself if possible.
20. If the lookup table does not change between sessions, configure the Lookup transformation to use a persistent lookup cache. The Informatica Server saves and reuses cache files from session to session, eliminating the time required to read the lookup table.
21. Use :LKP reference  qualifier in expressions only when calling unconnected Lookup Transformations.
22. Informatica Server generates an ORDER BY statement for a cached lookup that contains all lookup ports. By providing an override ORDER BY clause with fewer columns, session performance can be improved.
23. Eliminate unnecessary data type conversions from mappings.

Reduce the number of rows being cached by using the Lookup SQL Override option to add a WHERE clause to the default SQL statement.