Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
amit_saini
Honored Contributor III

Incremental load issue!

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:MISmiley FrustratedS') 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:MISmiley FrustratedS');

           

            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

1 Reply

Re: Incremental load issue!

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

Community Browser