How to do the performance tuning at various levels

[praveenb6]

Can any one tell me how to do the performance tuning at Source level, Target level, Mapping level and Session

Thanks
Dev

[jagadishwar]

You can improve performance at source leve using sqlquery and sorted input
and another way is to keep a filter transformation after sql transformation
Using partition type key range at source level.
Target level writing to file loads data faster than database. and use key range partition type at target level.
Tranasformations permformance can be improved using look up with dynamic cache and keeping erlier filter using Aggregator with sorted input ,remove unwanted ports from lookup,filter and other trasnformations
Session level Partioning helps u to improve performance.reading the session log details give where the bottleneck occured by cheking busy process and idle process. if the busy percntage is more than idle percentage it is a bottleneck.
I hope these are bottlenecks occured in informatica and using above technics improve performance

[ggk.krishna]

Hi Praveen,
First we have to identify the bottlenecks and then optimize.

1. Target : Load the data to a table and a flat file. If there is a time difference we can say that there is a target bottleneck.

Use target load type BULK.
Drop indexes and key constraints before loading and re-built them after the loading.

2. Source: For flat file sources mostly bottlenecks don't araise. If so increase the no. of bytes power center reads per line (default 1024 bytes) in the session properties.
If source is a table, we can identify using filter conditions and sql qery execution. Use filter transformation and set the condition as FALSE and observe the time.
Execute the sql query in the source database, observe the time taken for retrieving the rows.
Optimize the sql query in the SQL override and use conditional filters.
3.Mapping : We can identify the bottlenecks using conditional filters.
More lookups in the mapping reduces performance.
Avoid unnecessary data type conversions.
Remove unnecessary pipe lines and output ports.
4.Session: Use "Collect performance details" option in the session properties, so that we can get performance details log.In that any value other than zero for read to disk and write to disk, then there is a bottlneck.
Increase the commit intervals and cache size.
Set the tracing level Normal or Terse.
5.System : Using some tools like Task manager we can identify.
Improve the system configuration and network.

If any corrections needed give reply.


Thanks & Regards,
G Gopi Krishna.