Optimization in Informatica | Interview Question

OPTIMIZING TARGET:

• Defining constraints and indexes will slow down the loading of data. So drop indexes and key constraints.
• Use bulk loading. It will load the data without writing database logs which makes it more efficient. 

OPTIMIZING SOURCE:

• Do not use ORDER BY or GROUP BY since it creates index on the source.
• Use fast export reader instead of relation reader for source.

OPTIMIZING MAPPING:

• Add as much as filter whenever it is required.

OPTIMIZING TRANSFORMATION:

AGGREGATOR:

1. Use sorted input option
2. Use simple values for GROUP BY columns
3. Filter data before using the aggregator
4. Use incremental aggregation option

JOINER:

1. Design the master source with fewer duplicate key value
2. Perform join in the database whenever possible
3. Use sorted data 

LOOKUP:

1. If the lookup table database is the same as the source, then perform join in the database itself
2. Filter the filter rows
3. Optimize the lookup condition

NORMALIZER:

1. Place the normalizer close to the target.

SEQUENCE GENERATOR:

1. Make the transformation reusable

SORTER:

1. Allocate enough memory to sort the data

SOURCE QUALIFIER:

1. Add filter as much as possible
2. Use distinct option