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. POWERCENTERHELP
  14. Performance Counters

Performance Tuning Guide

Performance Tuning Guide

Overriding the ORDER BY Statement

Overriding the ORDER BY Statement

By default, the Integration Service generates an ORDER BY statement for a cached lookup. The ORDER BY statement contains all lookup ports. To increase performance, suppress the default ORDER BY statement and enter an override ORDER BY with fewer columns.
The Integration Service always generates an ORDER BY statement, even if you enter one in the override. Place two dashes ‘--’ after the ORDER BY override to suppress the generated ORDER BY statement.
For example, a Lookup transformation uses the following lookup condition:
ITEM_ID = IN_ITEM_ID PRICE <= IN_PRICE
The Lookup transformation includes three lookup ports used in the mapping, ITEM_ID, ITEM_NAME, and PRICE. When you enter the ORDER BY statement, enter the columns in the same order as the ports in the lookup condition. You must also enclose all database reserved words in quotes.
Enter the following lookup query in the lookup SQL override:
SELECT ITEMS_DIM.ITEM_NAME, ITEMS_DIM.PRICE, ITEMS_DIM.ITEM_ID FROM ITEMS_DIM ORDER BY ITEMS_DIM.ITEM_ID, ITEMS_DIM.PRICE --