A BUFFER prefix on a LOAD or SQL statement creates and maintains an automatic QVD for that statement. Subsequent executions of the LOAD/SELECT statement will read from the QVD, avoiding another (slower) trip to the database. A read from QVD is generally 5-10 times faster than fetching from database.
TranTab:
BUFFER LOADÂ
TranID,
Amount,
CustomerID,
etc…
;
SQL SELECT * FROM TRANSACTIONS
;
On first execution, the SELECT will fetch rows from the database and the resulting TranTab will be stored in a specially named QVD on the local machine. On subsequent reloads, TranTab will automatically be loaded from the local QVD.
If you make a change to the TranTab LOAD/SQL statement, QV Reload will detect the change and fetch from the database again and update the local QVD.
During script development it’s not uncommon to perform a reload several times. You can greatly reduce the duration of a script run by adding BUFFER to your statements. Any script changes/adds you make will automatically invalidate that buffer and re-fetch from the database.
Don’t forget to remove the BUFFER keyword before moving to production!
You can read more about BUFFER and some optional parameters in the Qlikview Help.
-Rob
Hi,why is it necessary to remove Buffer prefix in production?
When BUFFER is present, the data will be loaded form the local QVD. In production, you generally want to load from the database each time.
I always use Buffer combined with Stale, makes it very simple to remove when moving from dev to prod – just set StaleDays=0 instead.
Stale keeps track of how old you buffer is, when the time limit is reached it automatically refreshes and keeps that buffer for x number of days.
SET StaleDays=10;
BUFFER (Stale after $(StaleDays))
LOAD
now() as timestamp
AutoGenerate(1);
I have also used BUFFER for LOAD RESIDENT and LOAD FROM (QVD) LOAD statements. The improvement there depends on what level of processing you are doing combined with the performance and availability of the resources e.g. a slow disk reloading BUFFER QVD wont help with a LOAD RESIDENT. Using this idea throughout for a complex process got it down from 45 minutes to 20. A little quirk though, BUFFER on a LOAD FROM QVD statement does not work if the LOAD statement has a WHERE clause. Use preceding LOAD e.g. [ LOAD * ; LOAD * FROM filename.QVD (qvd) WHERE something=something ] to workaround issue. Lastly DEBUG/LIMIT LOAD or FIRST XXX rows syntax will also cause BUFFER QVD to “expire” since technically you changed the LOAD statement even if it doesnt seem like that to us.
Isn’t it better to have a separate QVW file for each database which retrieves data from the database and stores into a QVD file? This way, the following QVW files in the data pipeline can load from the QVD file, during development whenever required and also in production. So you don’t have to bother about making changes when going to production.
If everything is well segmented into multiple tiers and qvw per table you may not find BUFFER useful.
Quick question on this, where in the system is the QVD stored when the Buffer Statement does not explicitly provide a file path. Thank you
Do you mean the buffer QVD? The buffer location is a special folder and you can’t override it in the buffer load statement.
The buffer folder location varies by platform. it’s where the rest of the working files are for Qlik.
QV Desktop:
C:\Users\userid\AppData\Local\QlikTech\QlikView\Buffers.
QV Server: C:\ProgramData\QlikTech\QlikView\Buffers (I think)
QSE: C:\ProgramData\Qlik\Sense\Engine\Buffers