1 Reply Latest reply: Apr 18, 2016 9:08 AM by Marcus Sommer RSS

    Incremental load issue!

    Amit Saini

      Hi Folks ,

       

      Below is my script from some developer , currently this taking 2 hours to generate QVD's and I believe not doing any incremental load.

       

      So please help me out what can I do to reduce this loading time , below is the script:

       

       

      // incrementalLoad: 1 = Yes, 0 = No

      // incrementalLoadMode: 1 = only insert 2 = insert and update 3 = insert, update and delete

      sub LoadTable(tableName, uniquekey, incrementalLoad, multilanguage, incrementalLoadMode)

       

          if $(incrementalLoad) = 1 then

         

              // Loading Data from QVD

              $(tableName):

              LOAD DTUPDATE

              FROM $(QvdRawDataFolder)\$(tableName)_R.qvd (qvd);

             

              // Find Last Modified Date

              Last_Updated_Date:

              LOAD MAX(DTUPDATE) as MaxDate

              Resident $(tableName);   

             

              //Store Last Modified Date to a Variable

              Let Last_Updated_Date = Timestamp(peek('MaxDate', 0, 'Last_Updated_Date'));

             

              // Delete table

              Drop Table $(tableName);

             

              if $(multilanguage) > 0 then

             

                  // Daten aus der Datenbank laden. Unter berücksichtigung von dtupdate.

                  $(tableName):

                  LOAD *;

                  SQL SELECT $(uniquekey) AS UNIQUEKEY, $(tableName).* FROM $(tableName)

                  WHERE DTUPDATE > to_date('$(Last_Updated_Date)', 'dd.mm.yyyy HH24:MI:SS') and nlgeken = $(multilanguage);

                 

                  if $(incrementalLoadMode) = 1 then

                      // Datensätze hinzufügen

                      Concatenate ($(tableName))

                      LOAD *

                      FROM $(QvdRawDataFolder)\$(tableName)_R.qvd (qvd);           

                  else

                      // Datensätze hinzufügen und updaten

                      Concatenate ($(tableName))

                      LOAD *

                      FROM $(QvdRawDataFolder)\$(tableName)_R.qvd (qvd)

                      WHERE NOT Exists(UNIQUEKEY); //Durch das NOT Exists bekommt das Statement auch die Updates mit.

                  end if

                 

                  if $(incrementalLoadMode) = 3 then

                      // Hier werden gelöschte Datensätze aus der Tabelle gelöscht.

                      INNER JOIN ($(tableName)) LOAD UNIQUEKEY;

                      SQL SELECT $(uniquekey) AS UNIQUEKEY FROM $(tableName)

                      ;

                  end if

              else

             

                  // Daten aus der Datenbank laden. Unter berücksichtigung von dtupdate.

                  $(tableName):

                  LOAD *;

                  SQL SELECT $(uniquekey) AS UNIQUEKEY, $(tableName).* FROM $(tableName)

                  WHERE DTUPDATE > to_date('$(Last_Updated_Date)', 'dd.mm.yyyy HH24:MI:SS');

                 

                  if $(incrementalLoadMode) = 1 then

                      // Datensätze hinzufügen

                      Concatenate ($(tableName))

                      LOAD *

                      FROM $(QvdRawDataFolder)\$(tableName)_R.qvd (qvd);           

                  else

                      // Datensätze hinzufügen und updaten

                      Concatenate ($(tableName))

                      LOAD *

                      FROM $(QvdRawDataFolder)\$(tableName)_R.qvd (qvd)

                      WHERE NOT Exists(UNIQUEKEY); //Durch das NOT Exists bekommt das Statement auch die Updates mit.

                  end if

                 

                  if $(incrementalLoadMode) = 3 then

                      // Hier werden gelöschte Datensätze aus der Tabelle gelöscht.

                      INNER JOIN ($(tableName)) LOAD UNIQUEKEY;

                      SQL SELECT $(uniquekey) AS UNIQUEKEY FROM $(tableName)

                      ;

                  end if

             

              end if;

             

          else

         

              if $(multilanguage) > 0 then

                  if len('$(uniquekey)') > 0 then

                      $(tableName):

                      SQL SELECT $(uniquekey) AS UNIQUEKEY, $(tableName).* FROM $(tableName) WHERE nlgeken = $(multilanguage);

                  else

                      $(tableName):

                      SQL SELECT * FROM $(tableName) WHERE nlgeken = $(multilanguage);

                  end if       

              else

                  if len('$(uniquekey)') > 0 then

                      $(tableName):

                      SQL SELECT $(uniquekey) AS UNIQUEKEY, $(tableName).* FROM $(tableName);

                  else

                      $(tableName):

                      SQL SELECT * FROM $(tableName);

                  end if       

              end if;

             

          end if;

         

          STORE $(tableName) INTO $(QvdRawDataFolder)\$(tableName)_R.qvd (qvd);

         

          // Remove my in-memory database;

          DROP TABLE $(tableName);

         

      end sub

       

      Thanks,

      AS

        • Re: Incremental load issue!
          Marcus Sommer

          I think you should take a look into the log-file to see which parts runs by which variable-parameter (maybe a few additionally trace-statements might be helpful, too) and also how long the different loading are (you could then focus on the larger loadings).

           

          If this isn't clear enough respectively the results not sufficient then you could spilt the various script-parts into single loadings or to run the whole script within the debugger maybe with reduced on 1000 records - you might need some backup from your origin qvd's and/or running the sql's against a sql-dump to avoid any potential error's on them.

           

          - Marcus