CODEWITHSUNDEEP
sessioninsert-updateinformatica-powercenter
Jason
Jason

Reputation: 93

Powercenter (or informatica)performance issue

My powercenter version is 8.6.1. I designed a mapping and a session , and i checked "insert" and "update else insert" in the session settings. I found that the rate of data dealing was only 200 rows/s or so. By unchecking "update else insert", the rate could reach to 10000 rows/s. The target table had an index ，and i examined the execution plan, the index worked. And i executed update sql statement in DB (oracle), it took less than one second to update nearly 1 million rows. So it wouldn't be the DB problems. Did anyone run into the same problem？Is there something wrong in my session settings？ Any help would be pleasure！

Upvotes: 0

Views: 1827

Answers (3)

Aswin
Aswin

Reputation: 111

Yes. Bulk update is not possible in Informatica. Even if you set the mode to bulk, informatica will change it to Normal which you can see in session log.

Best way is, allow router to decide whether to insert or update the data before hitting the DB. You could you MD5() method to compare the data/columns real fast also try to utilize Dynamic lookup incase if you feel source contains duplicates.

Upvotes: 0

Daniel Machet
Daniel Machet

Reputation: 630

Have you checked the commit interval setting? Its default is usually around 10000 rows per commit. Worth pushing it up to a higher number your system can manage i.e. 100000 should becsafe and give an indication, most oracle systems can manage 1 million depending on the size of the target table rows. Yes Informatica has a commit interval but it is configurable

Upvotes: 0

Maciejg
Maciejg

Reputation: 3353

Informatica tries inserting. If it encounters an error, it's updating. For each and every row individually. Instead use a lookup and properly mark rows for insert or update. Use 'Data Driven' on session property. To increase the performance even more, verify if there are any changes - if row is exactly the same, filter it. This will perform updated only when it's really required, limiting the number of operations.

Upvotes: 1

Related Questions