optimizing incremental QVD load

[#1]

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;

[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.

[#3]

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);

[Gysbert_Wassenaar]

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');

Duh! I must have been more tired yesterday then I thought.

The last line should be: autogenerate(FieldValueCount('Zeitpunkt'));