Way to optimise a mapping on informatica

I would like to optimise a mapping developped by one of my colleague and where the "loading part" (in a flat file) is really really slow - 12 row per sec

Currently, to get to the point where I start writting in my file, I take about 2 hours, so I would like to know where I should start looking first otherwise, I will need at least 2 hours between each improvment - which is not really efficient.

Ok, so to describe simply what is done :

Oracle table (with big query inside - takes about 2 hours to get a result)
SQ
2 LKup on ref table (should not be heavy)
update strategy
1 transformer
2 Lk up (on big table - that should be one optimum point I guess : change them to joiner)
6 stored procedure (these also seem a bit heavy, what do you think ?)
another tranformer
load in the flat file

Can you confirm that either the LK up or the stored procedur part could be the reason why it is so slow ?

Do you think that I should look somewhere else to optimize ? I was thinking may be only 1 transformer.

Solution

First check the logs carefuly. Look at the timestamps. It should give you initial idea what part causes delay.

Lookups to big tables are not recommended. Joiners are a better way, but they still need to cache data. Can you limit the data for cache, perhaps? It'll be very hard to advise without seeing it.

Which leads us to the Stored Procedures: it's simply impossible to tell anything about them just like that.

So: first collect the stats and do log analysis. Next, read some tuning guides on the Net - there's plenty. Here's a more comprehensive one, but well... large - so you might like to try and look for some other ones. Powercenter Performance Tuning Guide