3 Replies Latest reply: Aug 31, 2015 1:44 PM by Gysbert Wassenaar RSS

    optimizing incremental QVD load

    Armin Rauscher

      Hello,

       

      i am trying to perfom an incremental QVD load, but it seems that the load takes longer as it would take to reload all data, can someone give me some hints to speed up my scripting?

       

      Thanks, Armin

       

      $(tableName1):

        LOAD * FROM $(QvdRawDataFolder)\$(tableName1)_R.qvd (qvd)

        WHERE RegionID=2;

       

        //Find max Zeitpunkt

        Max_Zeitpunkt:

        load max(Zeitpunkt) as MaxZeitpunkt

        resident $(tableName1);

       

        //Store Last modified Date to variable

        Let Max_Zeitpunkt_Var=timestamp(peek('MaxZeitpunkt',0,'Max_Zeitpunkt'), 'DD.MM.YYYY hh:mm:ss');

       

        Drop Table $(tableName1);

       

       

        Incremental:

        SQL SELECT ID as PartID,

          Zeitpunkt,

          Snr,

          ProductID,

          RegionID,

          State,

          ProductionMode,

          Reworke,

          Erstprf

        FROM $(tableName1)

        WHERE Zeitpunkt > '$(Max_Zeitpunkt_Var)' and (RegionID<=13 or RegionID=24);

        outer join (Incremental)

       

        SQL SELECT ID,

          Zeitpunkt as Zeitpunkt1,

          PartID,

          ValueID,

          ValueState,

          Value,

          ValueStr,

          State as State1,

              SpID

             FROM $(tableName2)

        WHERE Zeitpunkt > '$(Max_Zeitpunkt_Var)';

       

       

        //CONCATENATE

        Concatenate

        LOAD * FROM $(QvdRawDataFolder)\$(tableName1)_R.qvd (qvd);

       

       

        STORE Incremental into $(QvdRawDataFolder)\$(tableName1)_R.qvd (qvd);

       

       

        DROP Table Incremental;

        • Re: optimizing incremental QVD load
          Gysbert Wassenaar

          Well, you are loading the qvd data twice. And you're calculating the max(Zeitpunk) from a resident table. You can try this instead:

           

          Temp1:

          LOAD Zeitpunkt

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

          WHERE RegionID=2;

           

          Temp2;

          LOAD max(FieldValue('Zeitpunkt', RecNo())) as MaxZeitpunkt

          autogenerate(FieldValueCount('Zeitpunkt');

           

          LET Max_Zeitpunkt_Var = timestamp(peek('MaxZeitpunkt'), 'DD.MM.YYYY hh:mm:ss');

           

          DROP TABLES Temp1, Temp2;

           

          About the outer join, perhaps you can create one sql statement that does both at the same time and moves the execution of the join calculations to the source dbms. You'll have to see if that helps or not. It is possible that performance will improve, but the reverse is also possible.

            • Re: optimizing incremental QVD load
              Armin Rauscher

              Hello,

               

              Thanks for the information, i tried your script, but i get error message for

               

              Temp2;

              LOAD max(FieldValue('Zeitpunkt', RecNo())) as MaxZeitpunkt

              autogenerate(FieldValueCount('Temp');

               

              and i am unclear why.

               

              My complete new script looks like

               

              Temp1:

                LOAD Zeitpunkt FROM $(QvdRawDataFolder)\$(tableName1)_R.qvd (qvd)

                WHERE RegionID=2;

               

                //Find max Zeitpunkt

                Temp2:

                LOAD max(FieldValue('Zeitpunkt', RecNo())) as MaxZeitpunkt

                autogenerate(FieldValueCount('Temp'));

               

                LET Max_Zeitpunkt_Var = timestamp(peek('MaxZeitpunkt'), 'DD.MM.YYYY hh:mm:ss');

               

                Drop Tables Temp1, Temp2;

               

               

                $(tableName1):

                SQL SELECT ID as PartID,

                  Zeitpunkt,

                  Snr,

                  ProductID,

                  RegionID,

                  State,

                  ProductionMode,

                  Reworke,

                  Erstprf

                FROM $(tableName1)

                WHERE Zeitpunkt > '$(Max_Zeitpunkt_Var)' and (RegionID<=13 or RegionID=24);

                //CONCATENATE

                Concatenate

                LOAD * FROM $(QvdRawDataFolder)\$(tableName1)_R.qvd (qvd);

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

               

                // Remove my in-memory database;

                DROP TABLE $(tableName1);

               

                $(tableName2):

                SQL SELECT ID,

                  Zeitpunkt as Zeitpunkt1,

                  PartID,

                  ValueID,

                  ValueState,

                  Value,

                  ValueStr,

                  State as State1,

                      SpID

                     FROM $(tableName2)

                WHERE Zeitpunkt > '$(Max_Zeitpunkt_Var)';

               

               

                //CONCATENATE

                Concatenate

                LOAD * FROM $(QvdRawDataFolder)\$(tableName1)_R.qvd (qvd);

               

               

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

               

                // Remove my in-memory database;

                DROP TABLE $(tableName2);