Optimizing the Bottleneck’s

1. If the source is a flat file, ensure that the flat file is local to the Informatica server. If source is a relational table, then try not to use synonyms or aliases.
2. If the source is a flat file, reduce the number of bytes (By default it is 1024 bytes per line) the Informatica reads per line. If we do this, we can decrease the Line Sequential Buffer Length setting of the session properties.
3. If possible, give a conditional query in the source qualifier so that the records are filtered off as soon as possible in the process.
4. In the source qualifier, if the query has ORDER BY or GROUP BY, then create an index on the source table and order by the index field of the source table.

PERFORMANCE TUNING OF TARGETS

If the target is a flat file, ensure that the flat file is local to the Informatica server. If target is a relational table, then try not to use synonyms or aliases.

1. Use bulk load whenever possible.
2. Increase the commit level.
3. Drop constraints and indexes of the table before loading.

PERFORMANCE TUNING OF MAPPINGS

Mapping helps to channel the flow of data from source to target with all the transformations in between. Mapping is the skeleton of Informatica loading process.

1. Avoid executing major sql queries from mapplets or mappings.
2. Use optimized queries when we are using them.
3. Reduce the number of transformations in the mapping. Active transformations like rank, joiner, filter, aggregator etc should be used as less as possible.
4. Remove all the unnecessary links between the transformations from mapping.
5. If a single mapping contains many targets, then dividing them into separate mappings can improve performance.
6. If we need to use a single source more than once in a mapping, then keep only one source and source qualifier in the mapping. Then create different data flows as required into different targets or same target.
7. If a session joins many source tables in one source qualifier, then an optimizing query will improve performance.
8. In the sql query that Informatica generates, ORDERBY will be present. Remove the ORDER BY clause if not needed or at least reduce the number of column names in that list. For better performance it is best to order by the index field of that table.
9. Combine the mappings that use same set of source data.
10. On a mapping, field with the same information should be given the same type and length throughout the mapping. Otherwise time will be spent on field conversions.
11. Instead of doing complex calculation in query, use an expression transformer and do the calculation in the mapping.
12. If data is passing through multiple staging areas, removing the staging area will increase performance.
13. Stored procedures reduce performance. Try to keep the stored procedures simple in the mappings.
14. Unnecessary data type conversions should be avoided since the data type conversions impact performance.
15. Transformation errors result in performance degradation. Try running the mapping after removing all transformations. If it is taking significantly less time than with the transformations, then we have to fine-tune the transformation.
16. Keep database interactions as less as possible.