Performance Tuning

Have you done any Performance tuning in informatica?

1)     Yes, One of my mapping was taking 3-4 hours to process 40 millions rows into staging table we don’t have any transformation inside the mapping its 1 to 1 mapping .Here nothing  is there to optimize the mapping so  I created session partitions using key range on effective date column. It improved performance lot, rather than 4 hours it was running in 30 minutes for entire 40millions.Using partitions DTM will creates multiple reader and writer threads.
2)   There  was one more scenario where I got very good performance in the mapping level .Rather than using lookup transformation if we can able to do outer join in the source qualifier query override  this will give you good  performance if both lookup table and source were in the same database. If lookup tables is huge volumes then creating cache is costly.

3)    And also if we can able to optimize mapping using less no of transformations always gives you good performance.

4)   If any mapping taking long time to execute then first we need to look in to source and target statistics in the monitor for the throughput and also find  out where exactly the bottle neck by looking busy percentage in the session log will come to know which transformation taking more time ,if your source query is the bottle neck then it will  show  in the end of the session log  as “query issued to database “that means there is  a performance issue in the source query.we need to tune the query using .

Informatica Session Log shows busy percentage

If we look into  session logs it shows busy percentage based on that we need to find out where is bottle neck.

***** RUN INFO FOR TGT LOAD ORDER GROUP [1], CONCURRENT SET [1] ****

Thread [READER_1_1_1] created for [the read stage] of partition point [SQ_ACW_PCBA_APPROVAL_STG] has completed: Total Run Time = [7.193083] secs, Total Idle Time = [0.000000] secs, Busy Percentage = [100.000000]

Thread [TRANSF_1_1_1] created for [the transformation stage] of partition point [SQ_ACW_PCBA_APPROVAL_STG] has completed. The total run time was insufficient for any meaningful statistics.

Thread [WRITER_1_*_1] created for [the write stage] of partition point [ACW_PCBA_APPROVAL_F1, ACW_PCBA_APPROVAL_F] has completed: Total Run Time = [0.806521] secs, Total Idle Time = [0.000000] secs, Busy Percentage = [100.000000]

If suppose I've to load 40 lacs records in the target table and the workflow
is taking about 10 - 11 hours to finish. I've already increased
the cache size to 128MB.
 There are no joiner, just lookups
and expression transformations

Ans:

(1) If the lookups have many records, try creating indexes
on the columns used in the lkp condition. And try
increasing the lookup cache.If this doesnt increase
the performance. If the target has any indexes disable
them in the target pre load and enable them in the
target post load.

(2) Three things you can do w.r.t it.

1. Increase the Commit intervals ( by default its 10000)
2. Use bulk mode instead of normal mode incase ur target doesn't have
primary keys or use pre and post session SQL to
implement the same (depending on the business req.)
3. Uses Key partitionning to load the data  faster.

(3)If your target consists key constraints and indexes u slow

the loading of data. To improve the session performance in

this case drop constraints and indexes before you  run the

session and rebuild them after completion of session.