7 Replies Latest reply: Feb 23, 2016 11:17 AM by Cesar Accardi RSS

    Buffer load in Qlik Sense Server

    Cesar Accardi

      Hi,

       

      I am trying to optimize some reloads by adding the BUFFER prefix to load statements, e.g:

       

      TABLE:

      BUFFER (stale after 1 days)

      LOAD

          *;

      SQL SELECT

          FIELD1,

          FIELD2,

          FIELD3

      FROM

          TABLE;

       

      As far as I understand Qlik Sense should create QVD files automatically for each table and only pull data from the database when they expire (after 24 hours in this case) or changes are made to the loading script. When the reload task finishes I can see that some qvd files are created on this folder: C:\ProgramData\Qlik\Sense\Engine\Buffers however as soon as a new reload starts (less than an hour later) the qvds are deleted. Is this some kind of bug or are there any limitations for the BUFFER loading in Qlik Sense Server?

       

      Thanks,


      Cesar

        • Re: Buffer load in Qlik Sense Server
          Cesar Accardi

          Just to add more information to this... I have created a new application from scratch with only a simple table loading and the buffer load seems to work fine, so there must be something on the script of the other application that prevents buffer loading. Does anybody have any thoughts on this?

            • Re: Buffer load in Qlik Sense Server
              Jonathan Dienst

              Is any part of the LOAD/SELECT statements dynamic? The buffer uses a hash of these statements to determine if they represent the same query. The hash could be different of the actual executed statement is slightly different and this would cause the buffer qvd to be orphaned and deleted.

                • Re: Buffer load in Qlik Sense Server
                  Cesar Accardi

                  Hi Jonathan,

                   

                  There are a couple of SQL queries using a variable for the database name, so depending on what is set to the variable it switches the database to load from. Do you think this could be the cause for the issue?

                  • Re: Buffer load in Qlik Sense Server
                    Colin Albert

                    As jontydkpi says, BUFFER creates a dynamic filename for the QVD based on a hash of the load part of the script, so if the load script changes then a new QVD will be generated.

                    I consider BUFFER as a "quick & dirty" fix when developing and testing scripts. For production use a more robust incremental load process should be considered, especially if you are basing the data load on variables. This is because whenever a variable changes, then the buffered data will be ignored and new data loaded from the database.

                     

                    There is a list of sources on incremental load here Incremental Load in Qlikview - Sources

                    although this is QlikView based, the logic will be similar in Sense.

                      • Re: Buffer load in Qlik Sense Server
                        Cesar Accardi

                        Hi Colin,

                        Thanks for your reply. Since the variables haven't changed or the script itself I would expect the same hash value to be produced and data loaded from the existing qvd files, however this doesn't seem to be the case. In the Qlik Sense help it says buffer load has 'numerous limitations' but it doesn't give further details unfortunately:

                         

                        Limitations:

                        Numerous limitations exist, most notable is that there must be either a file LOAD or a SELECT statement at the core of any complex statement.

                         

                        The application I am working on doesn't require an incremental process since the data is always loaded completely from the database. I just wanted to have an expiration time for the qvd file to speed up loading, but if the buffer option doesn't work under certain conditions then I guess will have to implement that control on the script.