Recommended Performance Tuning Procedures

Challenge

To optimize PowerCenter load times by employing a series of performance tuning procedures.

Description

When a PowerCenter session or workflow is not performing at the expected or desired speed, there is a way to diagnose problems that may be adversely affecting various components of the data integration architecture. While PowerCenter has its own performance settings that can be tuned, you must consider the entire data integration architecture, including the Linux/Unix/Windows servers, network, disk array, and the source and target databases to achieve optimal performance. More often than not, an issue external to PowerCenter is the cause of the performance problem. In order to correctly and scientifically determine the most logical cause, the performance tuning steps should be executed in a specific order. This enables the ability to methodically rule out individual pieces and narrow down the specific areas on which to focus tuning efforts.

1. Perform Benchmarking

Always have a baseline of current load times for a given workflow or session with a similar row count. Maybe you are not achieving your required load window or simply think your processes could run more efficiently based on comparison with other similar tasks running faster. Use the benchmark to estimate what your desired performance goal should be and tune to that goal. Begin with the problem mapping that you created, along with a session and workflow that use all default settings. This helps to identify which changes have a positive impact on performance.

2. Identify the Performance Bottleneck Area

This step helps to narrow down the areas on which to focus further. Follow the areas and sequence below when attempting to identify the bottleneck:

• Target
• Source
• Mapping
• Session/Workflow

Use a series of tests with PowerCenter to identify trends that point where next to focus. Remember to go through these tests in a scientific manner; running them multiple times before reaching any conclusion and keeping in mind that fixing one bottleneck area may create a different bottleneck. For more information, see Determining Bottlenecks.

3. "Inside" or "Outside" PowerCenter

Depending on the results of the bottleneck tests, optimize inside or outside PowerCenter. Be sure to perform the bottleneck test in the order prescribed in Determining Bottlenecks, since this is also the order in which you should make any performance changes.

Problems outside PowerCenter refers to anything that indicates the source of the performance problem is external to PowerCenter. The most common performance problems outside PowerCenter are source/target database problems, network bottleneck, server, or operating system problems.

Problems inside PowerCenter refer to anything that PowerCenter controls, such as actual transformation logic, and PowerCenter Workflow/Session settings. The session settings contain quite a few memory settings and partitioning options that can greatly improve performance.

Although there are certain procedures to follow to optimize mappings, keep in mind that, in most cases, the mapping design is dictated by business logic; there may be a more efficient way to perform the business logic within the mapping, but you cannot ignore the necessary business logic to improve performance.

4. Re-Execute the Problem Workflow or Session

After you have completed the recommended steps for each relevant performance bottleneck, re-run the problem workflow or session and compare the results to the benchmark and compare load performance against the baseline. This step is iterative and should be performed after any performance-based setting is changed. You are trying to answer the question, Did the performance change have a positive impact?  If so, move on to the next bottleneck. Be sure to prepare detailed documentation at every step along the way so you have a clear record of what was and wasn't tried.

While it may seem like there are an enormous number of areas where a performance problem can arise, if you follow the steps for finding the bottleneck(s), and apply the tuning techniques specific to it, you are likely to improve performance and achieve your desired goals.