Search code examples
informatica-powercenter

2 mappings with exactly same session attributes behave completely different with slight change in logic

I am an informatica Developer. I have a mapping in informatica with below :

Original Mapping :

AS400(DB2SQ)->EXP->RTR->AGG1->MPLT->TGT1(SQL Server) Pipeline 1.
       |             |->AGG2->TGT2(SQL Server)
       |             |
       |             |->TGT3(SQL Server)
       ->AGG3->EXP->TGT4(FlatFile)           Pipeline 2.

Major number of records are passing through pipeline 1. And i was asked to optimize the flow. Below was my suggestions.

  1. In Pipeline 1. remove the AGG1 and AGG2, and push the aggregation logic to the database, this was my suggestion, as the flow is incremental, and incremental records being loaded into a temporary table, so expecting the performance to be better.
  2. Remove the Target data TGT3, as it was not required.

This is how my optimized mapping looks now :

Optimized Mapping(What i thought) :

AS400(DB2SQ)->EXP->RTR->MPLT->TGT1(SQL Server) Pipeline 1.
       |             |->TGT2(SQL Server)
       |
       ->AGG3->EXP->TGT4(FlatFile)           Pipeline 2.

Just to investigate on source performance optimizations, i replaced the sessions properties of all targets to write into a file instead. I wanted to check if i could optimize my source in anyways.

But to my surprise, when, i executed both the session(in separate workflows, and separately one after the other), i see that the SQ throughput for optimized session is much slower than the original session.

Everything in the optimized solution is exactly same, as i made a copy of the original mapping/session, before removing 2 of the Aggregators, and one of the target.

Please Note : the environment where i am developing has version control enabled, has it anything got with that?

I tried to cross check this multiple and unable to find an answer.

Solution

  • You can identify it better if you go through the session log in details.And also you can run the query in source dB and check for the time,it's taking.you can also tune the performance of the source side by using push down optimization (i.e. source side push down optimization). But before that check with your source dB if everything is good and it's not taking much time. Also you can modify optimize the query and see for the performance.

    If still that does not work out then you can go for session partitions in sq and check for the performance.