Massive Performance Degradation since 12.1 Upgrade
Has anyone else noticed a major slowdown in some of their reloads since upgrading from 11.2 to 12.1?
I have one app in particular that has a lot of complex/large JOINs to tables in the script, before that data is reduced and saved to a QVD. The app used to take 2mins 20secs on average to reload but since the upgrade, it's now taking around 15mins 44secs. That's 6.75 times longer than before which seem unusually high.
Thanks in advance for any assistance you can offer.
Transformation processes taking 6-7 times longer than in Qlikview 11.2. The app that causes the biggest problems has one particular table that needs to join to itself 12 times (using the same 2 fields each time) in order to generate a security key that is required for section access (using the hierarchy function doesn't complete all my requirements here, hence all the joins).
After the 3 passes using a LEFTJOIN(Table)DISTINCT, the size of the lines fetched starts to get significantly higher in 12.1 than in 11.2:
The result of these joins is a table that is 5,517,283DISTINCT rows of data that is LEFTJOINed to another table using a Unique Row Identifier. This same process in QV 11.2 only joins 907,148 rows of data.
I don't know when the series of LEFTJOINs are behaving differently but adding the DISTINCT to the final join (mentioned below the table above) reduced the load from 15:44 to 06:44.
By making the code more efficient (e.g. replacing a few WILDMATCH functions in the script), I've reduced the load by another 2 mins 16 seconds, bringing it down to 04:27.
I'm going to inform Qlik with my findings and I'll update this thread again if I make any more progress. I hope this helps you if you are experiencing similar issues.
In stead of using Left Joins (with Distinct as qualifier), why not try using a Mapping Table. The data is loaded just once into memory and you can apply that mapping any number of times you want. The only thing is that you will end up with a table with only 779,040 rows - the starting value. Mapping loads will not create new rows.
But, Mapping loads are 10 times more efficient than joins in Qlikview - with more predictable results.
Just one thing to keep in mind. If there's two rows that have the same KEY value, the first row will be used in the Mapping. So you might need to make the KEY unique.
Thanks for the reply Pieter. I agree with your suggestion but unfortunately, my problem isn't straight forward.
The purpose of the JOINS are to create a field that contains daily snapshot of an organisation hierarchy (created using Child ID & Parent ID fields). These fields are used to tag rows of data for use in a Hierarchy + Section Access. They need to look like this (bold IDs need to be created in script):
The relationships between child & parent can change at any given time (e.g. One day, the Child ID could be 1 and their parent could be 7, the next day it's parent could change to 12). As it changes so often, a child can have multiple parents over the data set's time period but they can only have one parent at a time so using joins for each day made sense.
If I were to use your approach, it would look like the attached but I don't think I'll have a change to change it now.
We had similar issues in customer environment. Ugrade from 11.2 to 12.2.
Application load time went from 40 minutes to 1 hour 40 minutes. Pain point here was loading a large qvd (18gb) with some where clauses. A quite simple load required significantly longer time to complete.
A ticket is pending a bug approval in Qlik support.