How do you improve the performance of lookup transformation?

·         Create an index on the columns used in the lookup condition

·         Place conditions with equality operator first

·         Cache small lookup tables.

·         Join tables in the database: If the source and the lookup table are in the same database, join the tables in the database rather than using a lookup transformation.

·         Use persistent cache for static lookups.

·         Avoid ORDER BY on all columns in the lookup source. Specify explicitly the ORDER BY clause on the required columns.

·         For flat file lookups, provide Sorted files as lookup source.