Performance Tuning – Aggregator Transformations

by kvt - Comment is Closed

Aggregator transformations often slow performance because they must group data before processing it. Aggregator transformations need additional memory to hold intermediate group results.

Use the following guidelines to optimize the performance of an Aggregator transformation:

  • Group by simple columns.
  • Use sorted input.
  • Use incremental aggregation.
  • Filter data before you aggregate it.
  • Limit port connections.

Grouping By Simple Columns

When possible, use numbers instead of string and dates in the columns used for the GROUP BY. Avoid complex expressions in the Aggregator expressions.

Using Sorted Input

​P​ass sorted data to the Aggregator transformation to increase session performance. When you use the Sorted Input option, the Integration Service assumes all data is sorted by group. As the Integration Service reads rows for a group, it performs aggregate calculations. When necessary, it stores group information in memory.

Using Incremental Aggregation
 
Use incremental aggregation to optimize the performance of Aggregator transformations. capture changes in the source and apply to the aggregator calculations and then updates the target incrementally, rather than processing the entire source and recalculating the same calculations every time you run the session. Increase the index and data cache sizes to hold all data in memory without paging to disk.
 
Filter data before you aggregate it
 
Place a filter transformation before the Aggregator transformation to reduce unnecessary aggregation.
 
Limit port connections
 
Connect only those ports which are really required for Aggregator transformation to avoid storing unwanted records to aggregator data cache.
 

Related Posts: