Have you ever wondered what would be the faster form of access for Qlikview? I have. I'm want to share my findings with you and hope it would be useful in your application development. Normally, whenever we require processing of information within a dashboard it is a common practice to load the data in to a resident table and then use the data from the table to perform the processing.
Resident Table
The resident tables are those that reside in the memory of the Qlikview server and is faster than querying the data sources each time you need to process data. The resident tables, though considered faster still leaves a lot to be desired in terms of speed - especially when the volume of data increases.
Resident tables can be useful when dealing with smaller volumes of data, but if the volumes are too large, it will be evident when the QVW runs through the reload cycles.
QVD
You probably know that QVD is a native data source exclusive to Qlikview which can be stored in the server and is considered as the best form of access when retrieving information data created by another QVW. But apparently you could also use QVDs to perform calculations within a QVW and trust me, this method works especially with higher volumes of data.
Real Life Example
In the industry I currently work in, the Qlikview dashboards deal with large volumes of data. One of the dashboards which reloads 33,000,000 million records were averaging around 46 minutes per reload cycle which by far was quite high for a QVW.
When analyzing the script, I found a resident table that loads data from 3 different tables and then the same resident table was used for further processing in 3 separate tabs each of which were performing many different calculations.
As this was a performance re-engineering task I did not have much liberty in altering the script as it would have resulted in altering many set expressions. However, I decided to try out the impact of storing the data in a QVD and then using that for further processing within the preceding tabs. The outcome was beyond belief!!!! The dashboard that averaged at 46 minutes now was recording an average of 2 minutes per reload!
Until this project I was under the impression that Resident table loads were faster compared to the QVDs, as the data sets used were smaller in volume and there was no visible difference. However, with the usage of QVDs to process a large volume of data, the results were in black and white.
Conclusion
Should we not use resident tables and stick to QVDs? I think no, as a rule of thumb I'm suggesting we consider the increase in the data used by the dashboard. If the data volumes are likely to increase by around 15% in about 6 months and if the current volume is already about 5,000,000 - a QVD solution would be more appropriate. However, any lesser number of records in my view would be well handled by the resident table.
