Informatica Performance Improvement Tips

We often come across situations where Data Transformation Manager (DTM) takes more time to read from Source or when writing in to a Target. Following standards/guidelines can improve the overall performance.

• Use Source Qualifier if the Source tables reside in the same schema
• Make use of Source Qualifier “Filter” Properties if the Source type is Relational.
• If the subsequent sessions are doing lookup on the same table, use persistent cache in the first session. Data remains in the Cache and available for the subsequent session for usage.
• Use flags as integer, as the integer comparison is faster than the string comparison.
• Use tables with lesser number of records as master table for joins.
• While reading from Flat files, define the appropriate data type instead of reading as String and converting.
• Have all Ports that are required connected to Subsequent Transformations else check whether we can remove these ports
• Suppress ORDER BY using the ‘–‘ at the end of the query in Lookup Transformations
• Minimize the number of Update strategies.
• Group by simple columns in transformations like Aggregate, Source Qualifier
• Use Router transformation in place of multiple Filter transformations.
• Turn off the Verbose Logging while moving the mappings to UAT/Production environment.
• For large volume of data drop index before loading and recreate indexes after load.
• For large of volume of records Use Bulk Load Increase the commit interval to a higher value large volume of data
• Set ‘Commit on Target’ in the sessions