The first step in performance tuning is to identify performance bottlenecks. Performance bottlenecks can occur in the source and target databases, the mapping, the mapping task, and the system.
Tuning a bottleneck may reveal another one. The strategy is to identify a performance bottleneck, eliminate it, and then identify the next performance bottleneck until you're satisfied with the performance.
Look for performance bottlenecks in the following order:
1Target
2Source
3Mapping
4Mapping task
5System
Use the following methods to identify performance bottlenecks:
Run test mappings.
You can configure a test mapping to read from a flat file source or to write to a flat file target to identify source and target bottlenecks.
Analyze thread statistics.
Analyze thread statistics to determine the optimal number of partitions.
Monitor system performance.
You can use system monitoring tools to view the percentage of CPU usage, I/O waits, and paging to identify system bottlenecks.
Thread statistics
You can use thread statistics in the session log to identify source, target, or transformation bottlenecks.
By default, a mapping uses one reader thread, one transformation thread, and one writer thread. The thread with the highest busy percentage identifies the bottleneck in the mapping run. When a specific transformation is identified as a bottleneck, you can tune the transformation.
The session log provides the following thread statistics:
Run time
Amount of time the thread runs.
Idle time
Amount of time the thread is idle, including the time the thread waits for other thread processing within the application. Idle time includes the time the thread is blocked by the Data Integration Server, but not the time the thread is blocked by the operating system.
Busy time
Percentage of the run time the thread is busy according to the following formula:
(Run time - idle time) / run time X 100
You can ignore high busy percentages when the total run time is short, such as under 60 seconds. This doesn't necessarily indicate a bottleneck.
To determine which transformation in the transformation thread is the bottleneck, view the busy percentage of each transformation in the thread work time breakdown. If the source, target, or transformations don't appear as the bottleneck, task properties could be causing the bottleneck.