I am trying to optimize some reloads by adding the BUFFER prefix to load statements, e.g:
BUFFER (stale after 1 days)
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\Buffershowever 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?
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?
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
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?
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.
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:
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.
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.