Informatica sorter transformation performance tunning related to cache allocation

Question

I am facing below warning for cache creation in session logs for sorter transformation in Informatica PowerCenter

**Warning in session log.* Sorter Transformation [HIGHYIELDSPRDDELTA] required 2-pass sort (1-pass temp I/O: 23224320 bytes). You may try to set the cache size to 30 MB or higher for 1-pass in-memory sort.

On the web, people suggest to calculate the size of data in the file and hard code it in properties of sorter transformation but that is not feasible in our project and this approach will not be a dynamic approach if data size increases the mapping will give an error.

I want a dynamic solution to tune the performance of the mapping, in short, I want to reduce the time of cache file creation. please suggest any other method to tune the mapping

Any help will appreciate.

Answer 1

It sounds like you haven't set the sorter cache size to Auto. Please check and amend.

Have a look at following link

https://kb.informatica.com/h2l/HowTo%2520Library/1/0794-OptimizingSessionCachesInPowerCenter-H2L.pdf&ved=0ahUKEwio7dHIz4TWAhVGFMAKHVW_BOIQFgglMAA&usg=AFQjCNH7O6r7Tmdps2bgQll5qAjga-FgvQ

i.e. the two settings on the config tab dynamically control how much memory is shared between all transformations set to auto cache size

Maximum memory allowed for Auto Cache attributes Maximum Percentage of Total Memory Allowed for Auto Memory Attributes

Answer 2

This warning means : all given data can't be sort at once as given cache/memory size is not enough. Data will be sorted in segments and combine later accordingly. You may face some performance issue some time. The best approach could be :

1. Read the sorted data if you are using any rational DB system provided your business logic not impacted.
2. Increase the cache memory size (rather than auto) to process max possible data.

hope this helps.