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