Two bits of Information I picked up from our principal Informatica Architect. When I used these, it did really work wonders.
- Aggregators and joiners should have sorted input. The Sorter Transformation sorts rows more efficiently that the Aggregator Transformation. This helps improve the performance of the workflow.
- Limit the number of Aggregators in a mapping. A high number of Aggregators can increase I/O activity on the cache directory. Unless the seek/access time is fast on the directory itself, having too many Aggregators can cause a bottleneck. Similarly, too many Lookups in a mapping causes contention of disk and memory, which can lead to thrashing, leaving insufficient memory to run a mapping efficiently.