I've been trying to research how to optimize my Informatica map's performance. I am trying to figure out if I should try to have my source qualifier do as much of the work as possible, so doing calculations, joining tables, handle conditional logic, or will my mappings perform better by using transformations such as expression transformation to handle calculations and other manipulations, joiner transformation for joining tables, and so on. I am also a bit new to Informatica.

CodePudding user response：

There is no right answer to your question. It depends on the power of your DBMS v. the power of your Informatica environment and how efficiently your DBMS performs each type of transformation compared to your Informatica environment.

The only way is to try it and see - on your specific environment.

CodePudding user response：

I think @NickW gave best answer, but here are my two cents.

If yor mapping has simple transformations like -

1. filter,
2. aggregation,
3. case-when/if else clause,
4. join 5/10 tables or have lookup on few tables in same DB

• you can use SQL. Normally DB can handle these kind of operations better than infa. Also, this will help less data transfer between DB and informatica so we can assume this will be faster.
  Factors to consider - if you have large table with no index, the SQL itself taking hours to return data, you can think about using informatica. In such scenario please run the query in DB and see if you can improve perf.
  Pls note informatica fetches physical data into infa server so in case of large table, this can be a problem for infa as well.

Now, if you have

1. complex mapping logic,
2. join with different DB, or lookup form different DB
3. File/XML/COBOL/XL source

• you need to use informatica only.