Incremental load issue!

amit_saini — Mon, 18 Apr 2016 10:51:26 GMT

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;

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

// Remove my in-memory database;

DROP TABLE $(tableName);

end sub

Thanks,

Re: Incremental load issue!

marcus_sommer — Mon, 18 Apr 2016 13:08:28 GMT

I think you should take a look into the log-file to see which parts runs by which variable-parameter (maybe a few additionally trace-statements might be helpful, too) and also how long the different loading are (you could then focus on the larger loadings).

If this isn't clear enough respectively the results not sufficient then you could spilt the various script-parts into single loadings or to run the whole script within the debugger maybe with reduced on 1000 records - you might need some backup from your origin qvd's and/or running the sql's against a sql-dump to avoid any potential error's on them.

- Marcus

topic Re: Incremental load issue! in QlikView

Incremental load issue!

Re: Incremental load issue!