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
