The first step would be examining the logs generated at INFA_HOME/logs/NodeName/services/DataIntegrationService/disLogs/profiling for errors and also timings to check what steps are taking the most time.
Is the source your mapping reads from a relational source or a flatfile source?
Are there many transformations before the Tx you run Profile Now on?
The source is a relational source. There aren't many transformations before the Exp. The expression transformation i created is on the read file(Relational). One of the Informatica blogs suggested to change the 'Maximum DB Connections' for better performance. I tried doing it and then ran the profile on the Exp transformation and it worked. Is that a good practice? is there a better solution than changing the Max DB connections?
It is one of the key properties governing profile performance for relational sources.
Others would be Maximum Profile Execution Pool Size as each column in a relational source as big as your example will require 1 mapping. So if this property is 10 (the default) then the maximum columns which profile concurrently is 10.
This value can be modified but must never be greater than Maximum Native Batch Execution Pool Size which governs the total mappings a DIS can run concurrently.
Thanks Robert. But I couldn't find 'Maximum Native Batch Execution Pool Size' on the DIS settings. Am i looking at a wrong place? What is the ideal value for 'Maximum Native Batch Execution Pool Size'?
The property has changed name a couple of times. What version are you using?
Regarding an ideal value, it's completely dependent on your use-cases, resources etc.