• Success
    Manage your Success Plans and Engagements, gain key insights into your implementation journey, and collaborate with your CSMs
    Success
    Accelerate your Purchase to Value engaging with Informatica Architects for Customer Success
  • Communities
    A collaborative platform to connect and grow with like-minded Informaticans across the globe
    Communities
    Connect and collaborate with Informatica experts and champions
    Have a question? Start a Discussion and get immediate answers you are looking for
    Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
  • Knowledge Center
    Troubleshooting documents, product guides, how to videos, best practices, and more
    Knowledge Center
    One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
    Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
    Information library of the latest product documents
    Best practices and use cases from the Implementation team
  • Learn
    Rich resources to help you leverage full capabilities of our products
    Learn
    Role-based training programs for the best ROI
    Get certified on Informatica products. Free, Foundation, or Professional
    Free and unlimited modules based on your expertise level and journey
    Self-guided, intuitive experience platform for outcome-focused product capabilities and use cases
  • Resources
    Library of content to help you leverage the best of Informatica products
    Resources
    Most popular webinars on product architecture, best practices, and more
    Product Availability Matrix statements of Informatica products
    Monthly support newsletter
    Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
    End of Life statements of Informatica products
Last Updated Date May 25, 2021 |

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.

Table of Contents

Success

Link Copied to Clipboard