Someone emailed me yesterday with a “never heard of”, at least for me, Informatica performance issue. The message said that they only have a sequence generator that is adding something to the data in the mapping. Everything else is coming straight from the source. The session took less than a minute to execute without a sequence generator and seven minutes with a sequence generator. It sounded a little odd because normally you would think that seq. gen. doesn’t make too much impact on the performance. But after a little investigation and a screenshot of the transformation properties we found out that a setting called “Number of Cached Values” was causing the issue. The value for it was set to 1 instead of recommended 0 (zero) or some large number. Setting this number to a small value could and would cause some issues for you. This setting is useful when there is a need to keep a unique set of values across mappings that share the same sequence generator. Usually these mappings are ran in parallel and the targets are same across all mappings that use this sequence generator.

This setting tells Informatica about the number of values that are stored in the repository when the session is started. The way Informatica processes this value is the key. Every time during the life of a session when this value is reached, Informatica queries the repository to get the next value for the Seq. Generator. So if you have this value set to 1, Informatica will query the repository database for every row that asks for a value from this transformation. And as you can imagine, this would definitely add some time to your the life of a session run.

If you set this number to some higher value like 15000, Informatica will query the repository after every 15000 rows. That may not necessarily be a bad thing. It would not have a major impact on total execution time. If the number is set to zero this setting would no more be a factor. Because then Informatica would NOT store the value of in the repository. Instead, it would be kept in the session memory.

The sequence number is kept on the server if the value is set to zero. In all other cases where it is set to be 1 or greater, the value is stored in the repository and the Informatica server process retrieves it from the database.

hope this helps…

Kirtan