After some new observations and some debates we had on this topic, we have decided recently to deepen a comparation regarding the execution time of some script parts that have similar results, but different ways of approaching it: adding of additional, calculated columns to a Qlik™ table.
We’ve chosen to compare as the first alternative, the so called preload, in which it is added, in front of the load command (or select command) , an additional command like:
load *, as NewField ;
The other option: the load & drop command, in which a table, already loaded resident with another name, is loaded again with the newly calculated fields, after which the initial table is deleted.
We compared the two commands on a normal computer, using the desktop versions of QlikView™ 11.2 SR8 and Qlik Sense™ 2.1.
As expected, the preload command is shorter and more elegant:
while this is the load & drop command:
Regarding the processing speed, for 1 million records, the preload command in QlikView™ had a total time of execution of 23 seconds,
while the load & drop alternative finished in 16 seconds, on the same data set.
In Qlik Sense™, the reading of the 1 million rows took less than 1 minute in the preload case,
and with 16 seconds faster with the load & drop.
After that we decided to repeat several times the measurements, in order to avoid any tricks that hardware resource utilization might fool us .
So we changed the two scripts, so they are executed automatically, on every 2 minutes.
The results were saved in a qvd file. After processing these results, we saw an execution time of 36 to 38 seconds for the preload command,
while the load & drop scenario, required only about 25-26 seconds to get executed.
Based on these facts, we can say that the preload command, despite the fact it is elegant and has a very strong functionality in data transformation within QlikView™, it increases the reload time with a significant percent of approximately 50%, compared to the load & drop command.
See the image below.
Still, if the reload time is not critical for you, the preload approach is bringing more readability, plus the option to have the QlikView™ functions used together with a SQL SELECT command and some additional WHERE, WHILE or GROUP BY clauses, if needed.
But we strongly recommend to avoid the preload option when the reload time is tight compared to the actual constraints and data sets contains hundreds of thousands of lines or more).
We wait your feedback and comments regarding this article over here or on our e-mail address:
office@qqinfo.ro
For information about Qlik™, please visit this site: qlik.com.
For specific and specialized solutions from QQinfo, please visit this page: QQsolutions.