Performance Tuning
Performance tuning questions ask you to find the bottleneck and fix it at the right layer: source, target, mapping, session or database.
Find the bottleneck first
Run the session and read Workflow Monitor's performance details: rows per second for the reader, the writer and each transformation. The slowest component is the bottleneck; tuning anything else wastes effort.
Source-side
Filter in the Source Qualifier rather than a downstream Filter so the database does the work. Use a user-defined join in the Source Qualifier instead of a Joiner when both tables live in the same database. Select only the columns you need, and use Number of Sorted Ports instead of a Sorter.
Target-side
Use bulk loading where supported (Oracle SQL*Loader, Teradata FastLoad/MultiLoad/TPump, DB2 Load, SQL Server bcp) by setting target load type to Bulk with a configured external loader. Teradata sessions can enable parallel mode. Dropping and rebuilding indexes around large loads usually beats row-by-row inserts.
Mapping-side
Narrow rows early, aggregate before joining when possible, use sorted input for Aggregator and Joiner, cache lookups (persistent, shared caches for small reference tables), give the Sequence Generator a cache, and consider pushdown optimization (source, target or full) so the database executes set-based logic.
Session-side
Partition large relational sources (round-robin, hash, key range, pass-through) to run concurrent pipelines, and tune DTM buffer size and commit interval against memory and rollback requirements.
Worked example
An Aggregator on 20 million rows runs for hours. Sorting through the Source Qualifier's Number of Sorted Ports and enabling Aggregator Sorted Input removes the Integration Service sort, which is usually the real cost.
Traps
- Sorted Input over unsorted data: fast but wrong.
- Expecting persistent lookup caches to refresh themselves; they stay stale until rebuilt.
- Bulk loading and then expecting checkpoint recovery to behave like a normal load.