1. Reduce the number of transformations. There is always overhead involved in moving data between transformations.
2. Consider more shared memory for large number of transformations. Session shared memory between 12MB and 40MB should suffice.
3. Calculate once, use many times.
o Avoid calculating or testing the same value over and over.
o Calculate it once in an expression, and set a True/False flag.
o Within an expression, use variable ports to calculate a value than can be used multiple times within that transformation.
4. Only connect what is used.
o Delete unnecessary links between transformations to minimize the amount of data moved, particularly in the Source Qualifier.
o This is also helpful for maintenance. If a transformation needs to be reconnected, it is best to only have necessary ports set as input and output to reconnect.
o In lookup transformations, change unused ports to be neither input nor output. This makes the transformations cleaner looking. It also makes the generated SQL override as small as possible, which cuts down on the amount of cache necessary and thereby improves performance.
5. Watch the data types.
o The engine automatically converts compatible types.
o Sometimes data conversion is excessive. Data types are automatically converted when types are different between connected ports. Minimize data type changes between transformations by planning data flow prior to developing the mapping.
6. Facilitate reuse.
o Plan for reusable transformations upfront.
o Use variables. Use both mapping variables as well as ports that are variables. Variable ports are especially beneficial when they can be used to calculate a complex expression or perform a disconnected lookup call only once instead of multiple times
o Use Mapplets to encapsulate multiple reusable transformations.
o Use Mapplets to leverage the work of critical developers and minimize mistakes when performing similar functions.
7. Only manipulate data that needs to be moved and transformed.
o Reduce the number of non-essential records that are passed through the entire mapping.
o Use active transformations that reduce the number of records as early in the mapping as possible (i.e., placing filters, aggregators as close to source as possible).
o Select appropriate driving/master table while using joins. The table with the lesser number of rows should be the driving/master table for a faster join.
8. Utilize single-pass reads.
o Redesign mappings to utilize one Source Qualifier to populate multiple targets. This way the server reads this source only once. If you have different Source Qualifiers for the same source (e.g., one for delete and one for update/insert), the server reads the source for each Source Qualifier.
o Remove or reduce field-level stored procedures.
o If you use field-level stored procedures, the PowerCenter server has to make a call to that stored procedure for every row, slowing performance.