Performance Takes Longer Time in preceding load when loading from QVD

[Lisa_Sun]

Preceding load based on QVD file as source takes significantly longer to load than when loading the same QVD file directly into memory.
 

TempTable: 
LOAD 
    *,
    Left(FirstName, 1) & Left(LastName, 1) AS Initials
;
LOAD
   *,
   Subfield(Name, ' ', 1) AS FirstName,
   Subfield(Name, ' ', 1) AS LastName
FROM [MyData.qvd] (QVD)
WHERE Region = 'Europe';

Environment:

Qlik Sense Enterprise on Windows 
QlikView 

Resolution:

The load performance is primarily an effect of the un-optimized QVD load that seeds the preceding load.

The performance depends on the expressions used and the size of the data being loaded.   The engine analyses the expression and determines if the expression is possible to execute over multiple threads. There will also be a decision made if the work is worth dividing on multiple cores, since multi threading requires additional work to divide and collect the results. Smaller work packages will not be scheduled to run in parallel since the overhead would be too high. 

The recommendation would be to use optimized loading rather than un-optimized reload.  Try and measure, then pick the method that gives the best performance for the expressions and dataset.  Remember to measure with the complete set of data before making the conclusion.

When developing reload script, it is recommended to always follow the best practice, which is to use optimized reload.  There are two solutions:

1. Use resident load

Resident Load:

TempTable:
LOAD *
FROM [MyData.qvd] (QVD)
WHERE Region = 'Europe';
 
Data:
LOAD 
    *,
    Left(FirstName, 1) & Left(LastName, 1) AS Initials
;
LOAD
   *,
   Subfield(Name, ' ', 1) AS FirstName,
   Subfield(Name, ' ', 1) AS LastName
Resident TempTable;
 
DROP Table TempTable;

2. Seed preceding load with optimized QVD load

• Generate required QVD in QVD layer.
• Apply WHERE clause that enables QVD to still load optimized.

Reference article Best Practice:  Loading QVD Files