Speed up Script Development with BUFFER

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.


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.


4 thoughts on “Speed up Script Development with BUFFER”

  1. 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))
    now() as timestamp

  2. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *