Accepted Answer

• Vote up

  1

  Vote down

  

  Replied by gsvasan on Wednesday, February 22 2012, 01:15 PM

  Hi,
  The operations for the following options will be done in the Database Level.
  - Update as Update
  - Update as Insert
  - Update else Insert
  It will write a 'select' statement on the target table and will compare with the source. Accordingly if the record already exits it will do an update else it will insert.
  
  On the other hand the update strategy the operations will be done at the informatica level itself. Finally the difference is the performance, using the update strategy will give you better performance.

  1 vote by saurav.mitra

  • 
    sugsez - more than a month ago

    Hi Gsvasan
    
    Want to understand one thing... In case of "update strategy", Informatica tags every row with DD_UPDATE flag and then sends the final UPDATE statement against the target, which looks like:
    
    UPDATE tgt_table
    SET coloumn1= :input_value1
    WHERE column2 = :input_value2
    
    Now there are 2 points to consider. First is - Informatica sends individual update statement for all the rows it needs to update in the target. So if you are updating 1 million rows, Informatica will send 1 million update statememnts to target - which may not be performance wise better when compared to a update statement like below:
    
    Update (select src.input_value1, src.input_value2, tgt.column1, tgt.column2
    from src_table src, tgt_table tgt
    where src.input_value2 = tgt.column2
    )
    set tgt.column1 = src.input_value1;
    
    The second point is - the use of "bind" variable in Informatica's Update statement generated by "Update Strategy". This might be the key to higher application performance as you have indicated above.
    When looking for a matching statement in the shared pool, only statements that exactly match the text of the statements are considered; so, if every SQL statement you submit is unique (only that the predicate changes) then you'll never get a match, and every statement you submit will need to be hard parsed. Hard parsing is very CPU intensive, and involves obtaining latches on key shared memory areas.
    
    So I believe, which one gives better performance is a bit debatable. Can any one in this forum do some data point experiments by using both the approaches in different data points and compare the performance?