Source:Create the Filter transformation after all the Source Qualifiers and make the filter condition FALSE so that the data will not go beyond this transformation. Then run the session and find out the time taken from source. If you feel there is some lack in performance, then suggest the necessary index creation in Pre Session. Note: If the source is File, then there is no possibility of performance problem in source side
Target: Delete the target table from the mapping and create the same structure as a Flat file. Run the session and find out the time taken to write the file. If you feel problem in performance, then delete the INDEX of the table before loading the data. In post Session, Create the same index Note:If the target is File, then there is no possibility of performance problem in target side
Mapping: The below steps need to be consider #1. Delete all the transformations and make it as single pass through #2. Avoid using more number of transformations #3. If you want to use more filter transformation, then use Router transformation instead of it #4. Calculate the index and data cache properly for Aggregation, Joiner, Ranker, Sorter if the Power centre is lower version. Advance version, Power centre itself will take care of this #5. Always pass the sorted i/p's to Aggregation #6. Use incremental aggregation #7. Do not do complex calculation in Aggregation transformation.
Session: Increase the DTM buffer size
System: #1. Increase the RAM capacity #2. Avoid paging