Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cesaraccardi
Specialist
Specialist

Buffer load in Qlik Sense Server

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

7 Replies
cesaraccardi
Specialist
Specialist
Author

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?

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
cesaraccardi
Specialist
Specialist
Author

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?

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.

cesaraccardi
Specialist
Specialist
Author

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.

Colin-Albert

If you just want to speed up loading, create a QVD generator app that loads from the database and stores the QVDs and has no charts for users; and a separate reporting app that always loads from QVDs. That way you have the control without the need to keep modifying your script.

cesaraccardi
Specialist
Specialist
Author

Yes, I guess will do that or either add some logic to check the creation time for the qvd files.