Informatica Performance Tuning

[Image* http://www.dwbiconcepts.com. Please check website for more information]

Optimizing the Buffer Block Size
Depending on the session source data, you might need to increase or decrease the buffer block size.

If the machine has limited physical memory and the mapping in the session contains a large number of sources, targets, or partitions, you might need to decrease the buffer block size.

If you are manipulating unusually large rows of data, increase the buffer block size to improve performance. If you do not know the approximate size of the rows, determine the configured row size by completing the following steps.

To evaluate needed buffer block size:

1. In the Mapping Designer, open the mapping for the session.

2. Open the target instance.

3. Click the Ports tab.

4. Add the precision for all columns in the target.

5. If you have more than one target in the mapping, repeat steps 2 to 4 for each additional target to calculate the precision for each target.

6. Repeat steps 2 to 5 for each source definition in the mapping.

7. Choose the largest precision of all the source and target precisions for the total precision in the buffer block size calculation.

The total precision represents the total bytes needed to move the largest row of data. For example, if the total precision equals 33,000, then the Integration Service requires 33,000 bytes in the buffers to move that row. If the buffer block size is 64,000 bytes, the Integration Service can move only one row at a time.

Ideally, a buffer accommodates at least 100 rows at a time. So if the total precision is greater than 32,000, increase the size of the buffers to improve performance.

To increase the buffer block size, open the session properties and click the Config Object tab. Edit the Default Buffer Block Size property in the Advanced settings.

Increase the DTM buffer block setting in relation to the size of the rows. As with DTM buffer memory allocation, increasing buffer block size should improve performance. If you do not see an increase, buffer block size is not a factor in session performance.

Increasing DTM Buffer Size
The DTM Buffer Size setting specifies the amount of memory the Integration Service uses as DTM buffer memory. The Integration Service uses DTM buffer memory to create the internal data structures and buffer blocks used to bring data into and out of the Integration Service. When you increase the DTM buffer memory, the Integration Service creates more buffer blocks, which improves performance during momentary slowdowns.

Increasing DTM buffer memory allocation generally causes performance to improve initially and then level off. When you increase the DTM buffer memory allocation, consider the total memory available on the Integration Service process system.

If you do not see a significant increase in performance, DTM buffer memory allocation is not a factor in session performance.

Note: Reducing the DTM buffer allocation can cause the session to fail early in the process because the Integration Service is unable to allocate memory to the required processes.

To increase the DTM buffer size, open the session properties and click the Properties tab. Edit the DTM Buffer Size property in the Performance settings.

The default for DTM Buffer Size is 12,000,000 bytes. Increase the property by multiples of the buffer block size, and then run and time the session after each increase.