Summary: Preceding load used to slow down your script. but no more. Beginning with QV Nov 2017, preceding load has no performance penalty.
I’ve posted several times about the elegance of preceding load. I’ve also written about how preceding load can make your script run significantly slower. Good news! Beginning with QV release Nov 2017 (12.20) the performance penalty has been eliminated.
To demonstrate the improvement, let me start with some test results from QV12.10 SR8, prior to the improvement.
Test 0, the first bar, indicates the time in seconds to perform an optimized load of a 20 million row QVD. Test 1, which follows, is loading the same QVD but with the addition of two new calculated fields in the same LOAD statement. The calculations are trivial, so the increase in elapsed time is mostly due to the loss of the optimized load.
Test 2 creates the same calculated fields using preceding load and you can see the dramatic increase in elapsed time. Test 5 adds a “LOAD *” to the preceding load stack and again shows a large increase in duration.
Tests 3, 4 & 6 repeat the same tests using Resident as the source instead of QVD. Once again, a significant increase in duration when preceding is used.
I’ve been running this same test suite for several years across multiple QV releases, different machines and varying datasets. The results are generally the same.
The problem, as explained to me by Henric Cronström and confirmed by my own observations, is that the preceding load code uses only a single processing thread. So while tests 1 & 3 above will use multiple threads, tests 2,4,5,6 will use only a single thread. One way to think of this is not that preceding load runs slower, but that non-preceding load runs faster.
I never did understand why Preceding-Resident ran slower than Preceding-QVD, but I no longer care!
Here I add test results (in red) for QV Nov 2017 SR1 (Qv 12.20) .
You can see optimized QVD (test 0) is about the same. Adding calculated fields (test 1) is slightly better between releases.
What is really significant is there is no longer any increase when using preceding load. Further, Resident performs faster than QVD as I would expect. (Note both tests used an SSD drive).
This is all great news as there are many cases where preceding load can help make your code more maintainable and understandable. I hated to choose between clarity and performance.
What about Qlik Sense? I’ve confirmed that Feb 2018 Desktop exhibits the new “no-penalty” performance. I don’t know about previous releases.
No reason to fear preceding load!
-Rob
Thank you for testing and sharing the good news!
QV Nov 2012 or Nov 2017???
Thanks, good catch. I meant 2017. Post corrected.
2017
This is great news and a very good reason to upgrade, thanks Rob!
Thanks Rob. That is good news indeed. I can now stop feeling bad about not advising training delegates about the performance hit – for someone learning Qlik I would say that tidy readable code trumps performance in most cases.
In fact, I often quote yourself and something you mentioned on the London Masters Summit about a premature optimisation being a waste of time.
How about GROUP BY within the preceding statement?
I just tested 12.20 (November 2017 SR3) and this takes 6 minutes (140M rows):
LOAD
myField,
otherField,
count(X)
GROUP BY
myField,
otherField
;
LOAD * FROM myFile.qvd (qvd);
..whereas this only takes 35 seconds:
temp1:
LOAD * FROM myFile.qvd (qvd);
temp2:
LOAD
myField,
otherField,
count(X)
RESIDENT temp1
GROUP BY
myField,
otherField;
DROP TABLE temp1;
I haven’t done any testing with GROUP BY, but will if I find time. Others have pointed out on Qlik Community that Group By can be optimized by sorting the table first, and perhaps there is some additional optimization for grouping resident input. Do you have any data on how your sample performs on previous releases?
It will be interesting to know the Group By performance. I haven’t checked recently but script Group By was using single cpu core and was quite slow for this reason.
Hello,
Is this verified by Qlik or stated by them as a performance improvement anywhere on their site?
Hi James,
I have not found anything in the release notes or any other statement by Qlik. I had an informal conversation or two with someone in Qlik who let me know this improvement was in the pipeline.
Hi Rob,
Thank you for sharing! It is interesting that resident triple preceding is faster than resident preceding in the 12.2 release. Can you expound on that?
Hi Shilpan,
My testing methodology is not precise enough to consider 12.38 seconds vs 12.34 a significant difference. In some other test runs it was a few hundredths the other direction.
Awesome. Thanks, Rob!
Good post!!!
Hi Rob,
That’s just in Qlik View, or did you get performance improvements for Qlik Sense too?
Qlik Sense is improved as well.