Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.
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);
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'));