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. Appendix A: Performance Counters

Performance Tuning Guide

Performance Tuning Guide

Optimizing the Query

Optimizing the Query

If a session joins multiple source tables in one Source Qualifier, you might be able to improve performance by optimizing the query with optimizing hints. Also, single table select statements with an ORDER BY or GROUP BY clause may benefit from optimization such as adding indexes.
Usually, the database optimizer determines the most efficient way to process the source data. However, you might know properties about the source tables that the database optimizer does not. The database administrator can create optimizer hints to tell the database how to execute the query for a particular set of source tables.
The query that the Integration Service uses to read data appears in the session log. You can also find the query in the Source Qualifier transformation. Have the database administrator analyze the query, and then create optimizer hints and indexes for the source tables.
Use optimizing hints if there is a long delay between when the query begins executing and when PowerCenter receives the first row of data. Configure optimizer hints to begin returning rows as quickly as possible, rather than returning all rows at once. This allows the Integration Service to process rows parallel with the query execution.
Queries that contain ORDER BY or GROUP BY clauses may benefit from creating an index on the ORDER BY or GROUP BY columns. Once you optimize the query, use the SQL override option to take full advantage of these modifications.
You can also configure the source database to run parallel queries to improve performance. For more information about configuring parallel queries, see the database documentation.