Table of Contents

Search

  1. Preface
  2. Performance Tuning Overview
  3. Bottlenecks
  4. Optimizing the Target
  5. Optimizing the Source
  6. Optimizing Mappings
  7. Optimizing Transformations
  8. Optimizing Sessions
  9. Optimizing Grid Deployments
  10. Optimizing the PowerCenter Components
  11. Optimizing the System
  12. Using Pipeline Partitions
  13. Performance Counters

Performance Tuning Guide

Performance Tuning Guide

Optimizing Joiner Transformations

Optimizing Joiner Transformations

Joiner transformations can slow performance because they need additional space at run time to hold intermediary results. You can view Joiner performance counter information to determine whether you need to optimize the Joiner transformations.
Use the following tips to improve session performance with the Joiner transformation:
  • Designate the master source as the source with fewer duplicate key values.
    When the Integration Service processes a sorted Joiner transformation, it caches rows for one hundred unique keys at a time. If the master source contains many rows with the same key value, the Integration Service must cache more rows, and performance can be slowed.
  • Designate the master source as the source with fewer rows.
    During a session, the Joiner transformation compares each row of the detail source against the master source. The fewer rows in the master, the fewer iterations of the join comparison occur, which speeds the join process.
  • Perform joins in a database when possible.
    Performing a join in a database is faster than performing a join in the session. The type of database join you use can affect performance. Normal joins are faster than outer joins and result in fewer rows. In some cases, you cannot perform the join in the database, such as joining tables from two different databases or flat file systems.
    To perform a join in a database, use the following options:
    • Create a pre-session stored procedure to join the tables in a database.
    • Use the Source Qualifier transformation to perform the join.
  • Join sorted data when possible.
    To improve session performance, configure the Joiner transformation to use sorted input. When you configure the Joiner transformation to use sorted data, the Integration Service improves performance by minimizing disk input and output. You see the greatest performance improvement when you work with large data sets. For an unsorted Joiner transformation, designate the source with fewer rows as the master source.