8 Replies Latest reply on Sep 9, 2021 2:54 PM by Nico Heinze

    how to update large historical table

    Shubha Prada New Member

      I have a scenario where target has large set of historical data and source has very few  updates every day.

      In this scenario what will be the best option to implement incremental load .

      If I do lookup on Target object then it will create performance issue as it has large volume of data.

       

      Please suggest suitable solution for this.

        • 1. Re: how to update large historical table
          Nico Heinze Guru

          Set up the lookup transformation to not create cache files (i.e. leave the option "Lookup caching enabled" blank instead of setting it).

          This way for each input record one SELECT statement will be issued. This shouldn't cause much performance trouble as long as the "history" of each input record doesn't consist of thousands of records.

           

          Regards,

          Nico

          • 2. Re: how to update large historical table
            Shubha Prada New Member

            Thanks @Nico Heinze for the response.

            • 3. Re: how to update large historical table
              Shubha Prada New Member

              Thank you @Nico Heinze

              • 4. Re: how to update large historical table
                Vlad Ponomarenko Guru

                Hi,

                Please, read Support

                Regards, Vlad

                • 5. Re: how to update large historical table
                  Nico Heinze Guru

                  While the feature Update Else Insert is a possibility, this is dangerous in some respect and bad for performance. The point is that each record is moved over the network one or three times.

                   

                  If all goes well and the record already exists, the record will be sent to the target database once and fine.

                   

                  If the record goes over the wire for a new record, then the UPDATE statement will be rejected by the target database (because the record does not yet exist) and the complete record will be returned to PowerCenter so that PowerCenter can log this record (which is not done in this case, but it's normal for a database to send back the complete record), so the INSERT statement has to be issued by PowerCenter and the record will be sent to the target a second time.

                  So the record will - in case of a new record - be sent over the wire three times. Which can cause notable performance issues.

                   

                  Not to forget that some databases (in particular DB2 on z/OS) log all failed requests in the DB logs, meaning that each insertion of a new record via Update Else Insert will be logged in the Db2 logs. And trust me, Db2 admins do not appreciate such unnecessary data volume, this causes them great headache.

                   

                  So Update Else Insert should be used with great care and with caution.

                   

                  Regards,

                  Nico

                  • 6. Re: how to update large historical table
                    Santosh Gade Guru

                    You can also do a SQ level join on you historic table and your staging table to identify insert and update records . This might be a bit faster than doing a lookup

                     

                    Also usually in such cases updates do take longer when done from Informatica . One solution I used in the past was to insert the updates into a temporary table and then use BTEQ to do the actual updates . (This was teradata) . May be something similar can help.

                    • 7. Re: how to update large historical table
                      Vlad Ponomarenko Guru

                      Hi Nico,

                       

                      May be you are right for  DB2. I did not work with this DB.
                      Oracle will return only result like "N rows updated".

                      When N=0 it is not the error, only result of operation. I don't understand why server DB must return the whole row to client in this case. It is very expensive and unneeded operation. Client knows sent row.

                       

                      Hence, for Oracle may be only one operation (N>0, the row(s) was updated) or two.

                      I don't think this is the best solution, but one of the possible ones.

                      Regards, Vlad

                      • 8. Re: how to update large historical table
                        Nico Heinze Guru

                        I agree that with Oracle things are different. As it was not indicated in the original post which DBMS is used, I wanted to make sure that Shubha doesn't fall victim to this potential pitfall of making the DBAs *very* angry with you. I have had this experience, and I am glad that they didn't get hold of me personally at this time.

                         

                        Regards,

                        Nico