- Inspect Informatica session logs for the number of lookups, including each lookup's percentage runtime.
- Check "Lookup table row count" and "Lookup cache row count" numbers for each Lookup Transformation. If Lookup table row count is too high, Informatica will cache a smaller subset in its Lookup Cache. Such lookup could cause significant performance overhead on ETL tier.
- If functional logic permits, consider reducing a large lookup row count by adding more constraining predicates to the lookup query WHERE clause.
- If a Reader Source Qualifier query is not a bottleneck in a slow mapping, and the mapping is overloaded with lookups, consider pushing lookups with row counts less than two million into the Reader SQL as OUTER JOINS.Some lookups could be reusable within a mapping or across multiple mappings, so they cannot be constrained or pushed down into Reader queries.
- If you identify a very large lookup with row count more than 15-20 million, consider pushing it down as an OUTER JOIN into the mapping’s Reader Query. Such update would slow down the Reader SQL execution, but it might improve overall mapping’s performance.
- If identify bottlenecks with lookups having very large rowcounts, Update lookup table query,which reduce lookup cache row count
Thursday, March 21, 2013
Informatica Lookups Performance monitoring
Too many Informatica Lookups in an Informatica mapping may cause significant performance slowdown.Review the guidelines below for handling Informatica Lookups in mappings.