Informatica self learning
Informatica PowerCenter Development Best Practices
Informatica PowerCenter Development Best Practices
1. Lookup - Performance considerations
What is a lookup transformation? It is just not another transformation that fetches you data to look up against source data. A Lookup is an important and useful transformation when used effectively. If used improperly, performance of your mapping will be severely impaired.
Let us see the different scenarios where you can face problems with Lookup and also how to tackle them.
1.1. Unwanted columns
By default, when you create a lookup on a table, PowerCenter gives you all the columns in the table. If not all the columns are required for the lookup condition or return, delete the unwanted columns from the transformations.
By not removing the unwanted columns, the cache size will increase.
1.2. Size of the source versus size of lookup
Let us say, you have 10 rows in the source and one of the columns has to be checked against a big table (1 million rows). Then PowerCenter builds the cache for the lookup table and then checks the 10 source rows against the cache. It takes more time to build the cache of 1 million rows than going to the database 10 times and lookup against the table directly.
Use uncached lookup instead of building the static cache, as the number of source rows is quite less than that of the lookup.
1.3. JOIN instead of Lookup
In the same context as above, if the Lookup transformation is after the source qualifier and there is no active transformation in-between, you can as well go for the SQL over ride of source qualifier and join traditionally to the lookup table using database joins, if both the tables are in the same database and schema. 1.4. Conditional call of lookup
Instead of going for connected lookups with filters for a conditional lookup call, go for unconnected lookup. Is the single column return bothering for this? Go ahead and change the SQL override to
Informatica PowerCenter Development Best Practices
1. Lookup - Performance considerations
What is a lookup transformation? It is just not another transformation that fetches you data to look up against source data. A Lookup is an important and useful transformation when used effectively. If used improperly, performance of your mapping will be severely impaired.
Let us see the different scenarios where you can face problems with Lookup and also how to tackle them.
1.1. Unwanted columns
By default, when you create a lookup on a table, PowerCenter gives you all the columns in the table. If not all the columns are required for the lookup condition or return, delete the unwanted columns from the transformations.
By not removing the unwanted columns, the cache size will increase.
1.2. Size of the source versus size of lookup
Let us say, you have 10 rows in the source and one of the columns has to be checked against a big table (1 million rows). Then PowerCenter builds the cache for the lookup table and then checks the 10 source rows against the cache. It takes more time to build the cache of 1 million rows than going to the database 10 times and lookup against the table directly.
Use uncached lookup instead of building the static cache, as the number of source rows is quite less than that of the lookup.
1.3. JOIN instead of Lookup
In the same context as above, if the Lookup transformation is after the source qualifier and there is no active transformation in-between, you can as well go for the SQL over ride of source qualifier and join traditionally to the lookup table using database joins, if both the tables are in the same database and schema. 1.4. Conditional call of lookup
Instead of going for connected lookups with filters for a conditional lookup call, go for unconnected lookup. Is the single column return bothering for this? Go ahead and change the SQL override to