topic Re: optimizing incremental QVD load in Archived Groups

optimizing incremental QVD load

Tue, 22 Jul 2025 17:59:49 GMT

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 — Wed, 15 Apr 2015 11:44:27 GMT

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

Wed, 15 Apr 2015 14:48:46 GMT

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

Re: optimizing incremental QVD load

Gysbert_Wassenaar — Thu, 16 Apr 2015 16:38:24 GMT

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