Informatica Performance Improvement Tips

Posted by Muralidhar Reddy
January 3rd, 2011

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 Qualifer  “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

Comments (0)