Improving aggregator transformation performence

In my last post i discussed about aggregate transformation.

So now lest see how can we improve performance of aggregator transformation.

Performence of aggregator transformation can be improved if we check the sorted input property of the aggregator.

If we check this property we are saying aggregator that we will be sending sorted input to it.So this will help the aggregator to group data quickly.

So who will sort data for aggregator?

Two of its friends Source Qualifier and Sorter can help Aggregator in sorting the data.

Lets see how it is done,

In source qualifier of this mapping set the property "no of sorted ports".

read below what Help Document of Informatica says about this property,

Indicates the number of columns used when sorting rows queried from relational sources. If you select this option, the Integration Service adds an ORDER BY to the default query when it reads source rows. The ORDER BY includes the number of ports specified, starting from the top of the transformation.

When selected, the database sort order must match the session sort order.

So far the above statement i made the DeptNo as the first port of source qualifier and set the Property to 1.

So you are done save the mapping and Refresh the session, by doing a right click on it and selecting "refresh mapping".

Try to use Sorter before aggregator and build mapping.

You can download mapping here.

You can download scripts here.