There are two methods of loading data from QVDs: optimized and non-optimized. The key point here is that the optimized loads can be up to 100 times quicker than the non-optimized loads.
This speed increase is a result of the data passing directly from the disk (QVD) into the memory (RAM) without being unpacked from its compressed QVD format.
As you may have guessed the reason every load is not optimized is because we often want to change the data coming out of the QVD. This requires it to be uncompressed before going into memory; hence, it is significantly slower. Just about any change to the data will cause the load to be non-optimized; however, there are a few things that we can do.
- Open a new QlikSense application and go straight to the data load editor.
- Create a folder library connection to any location where you want to save example data files and call that connection
QVDs.
- Copy the following code into the data load editor. Please note that if you are using a very low spec machine you can reduce the 20 million number on the third line to something smaller like 1 million:
ExampleData: Load RecNo() AS TransID Autogenerate 20000000; Store ExampleData into [lib://QVDs/Data1.qvd](qvd); Drop Table ExampleData; OptimizedLoad: LOAD TransID FROM [lib://QVDs/Data1.qvd](qvd); Store OptimizedLoad Into [lib://QVDs/Data2.qvd](qvd); Drop Table OptimizedLoad; UnoptimizedLoad: LOAD 'Example Text' AS NewField, TransID FROM [lib://QVDs/Data2.qvd](qvd) Where Not IsNull(TransID); - Reload the application and make a note of the time it takes to load the records in each table:
The first 20 million records loaded are simply auto-generated data records that we store in a Data1.qvd file to use later on. Now, we have a QVD available to read from, which we can use to demonstrate the difference between an optimized load and an un-optimized load. As a rule of thumb any data transformations on the QVD data in the script will cause the load to be un-optimized.
The second load of 20 million records simply reads the data from the Data1.qvd file (created in the preceding step) directly into memory and no further transformations take place. As no transformations take place in the Load statement, the load is an optimized load as stated in the Data progress window. We store the data loaded from this step into another QVD file called as Data2.qvd.
The third load is from the Data2.qvd file, the difference being that this time the script adds a Where clause and a new calculated field. Either of these transformations will cause Qlik Sense to use the unoptimized load method. Notice that the Data progress window does not specify "optimized load" even though we are loading the data from a QVD file.
You can think of optimized versus un-optimized loads as data being directly loaded into RAM for reading versus the unpacked data that is read line by line. A good exception to a Where clause that breaks the optimization rule is the Exists() function. Using Where Exists(<Field>) at the end of a load is a good method of loading the data that's relevant to what has been loaded previously.