Tuning Mappings part1

mapping-level optimization takes time to implement but can significantly boost performance. Sometimes the mapping is the biggest bottleneck in the load process because business rules determine the number and complexity of transformations in a mapping.
Before deciding on the best route to optimize the mapping architecture, you need to resolve some basic issues. Tuning mappings is a tiered process. The first tier can be of assistance almost universally, bringing about a performance increase in all scenarios. The second tier of tuning processes may yield only small performance increase, or can be of significant value, depending on the situation.
Some factors to consider when choosing tuning processes at the mapping level include the specific environment, software/ hardware limitations, and the number of records going through a mapping. This Best Practice offers some guidelines for tuning mappings.
Description
Analyze mappings for tuning only after you have tuned the system, source, and target for peak performance. To optimize mappings, you generally reduce the number of transformations in the mapping and delete unnecessary links between transformations.
For transformations that use data cache (such as Aggregator, Joiner, Rank, and Lookup transformations), limit connected input/output or output ports. Doing so can reduce the amount of data the transformations store in the data cache. Too many Lookups and Aggregators encumber performance because each requires index cache and data cache. Since both are fighting for memory space, decreasing the number of these transformations in a mapping can help improve speed. Splitting them up into different mappings is another option.
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.
Consider Single-Pass Reading
If several mappings use the same data source, consider a single-pass reading. Consolidate separate mappings into one mapping with either a single Source Qualifier Transformation or one set of Source Qualifier Transformations as the data source for the separate data flows.
Similarly, if a function is used in several mappings, a single-pass reading will reduce the number of times that function will be called in the session.
Optimize SQL Overrides
When SQL overrides are required in a Source Qualifier, Lookup Transformation, or in the update override of a target object, be sure the SQL statement is tuned. The extent to which and how SQL can be tuned depends on the underlying source or target database system.
Scrutinize Datatype Conversions
PowerCenter Server automatically makes conversions between compatible datatypes. When these conversions are performed unnecessarily performance slows. For example, if a mapping moves data from an Integer port to a Decimal port, then back to an Integer port, the conversion may be unnecessary.
In some instances however, datatype conversions can help improve performance. This is especially true when integer values are used in place of other datatypes for performing comparisons using Lookup and Filter transformations.
Eliminate Transformation Errors
Large numbers of evaluation errors significantly slow performance of the PowerCenter Server. During transformation errors, the PowerCenter Server engine pauses to determine the cause of the error, removes the row causing the error from the data flow, and logs the error in the session log.
Transformation errors can be caused by many things including: conversion errors, conflicting mapping logic, any condition that is specifically set up as an error, and so on. The session log can help point out the cause of these errors. If errors recur consistently for certain transformations, re-evaluate the constraints for these transformations. Any source of errors should be traced and eliminated.