By default, the Integration Service generates an ORDER BY clause for a cached lookup. The ORDER BY clause contains all lookup condition ports. To increase performance, you can suppress the default ORDER BY clause and enter an override ORDER BY with fewer columns.
The override SQL must return data sorted on the lookup keys. When the transformation retrieves all rows in a lookup, the Integration Service builds the data cache with the keys in sorted order. The Integration Service cannot retrieve all the rows from the cache if the rows are not sorted. If the data is not sorted on the keys, you might get unexpected results.
If you use pushdown optimization, you cannot override the ORDER BY clause or suppress the generated ORDER BY clause with a comment notation.
The Integration Service always generates an ORDER BY clause, even if you enter one in the override. Place two dashes ‘--’ after the ORDER BY override to suppress the generated ORDER BY clause. For example, a Lookup transformation uses the following lookup condition:
ITEM_ID = IN_ITEM_ID
PRICE <= IN_PRICE
The Lookup transformation includes two lookup condition ports used in the mapping, ITEM_ID, and PRICE. When you enter the ORDER BY clause with one or more columns, 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 AS ITEM_NAME, ITEMS_DIM.PRICE AS PRICE, ITEMS_DIM.ITEM_ID AS ITEM_ID FROM ITEMS_DIM ORDER BY ITEMS_DIM.ITEM_ID --
To override the default ORDER BY clause for a relational lookup, complete the following steps:
Generate the lookup query in the Lookup transformation.
Enter an ORDER BY clause that contains the condition ports in the same order they appear in the Lookup condition.
Place two dashes ‘--’ as a comment notation after the ORDER BY clause to suppress the ORDER BY clause that the Integration Service generates.
If you override the lookup query with an ORDER BY clause without adding comment notation, the lookup fails.
Sybase has a 16 column ORDER BY limitation. If the Lookup transformation has more than 16 lookup/output ports including the ports in the lookup condition, override the ORDER BY clause or use multiple Lookup transformations to query the lookup table.