Massive Performance Degradation since 12.1 Upgrade

[ciaran_mcgowan]

Hi all,

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.

[ciaran_mcgowan]

So I wanted to share my progress with you, just in case anyone else is experiencing similar issues.

Qlikview Server: 12.1 SR7

Potential Causes: QIX engine handles JOINS differently

Issue:

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 LEFT JOIN (Table) DISTINCT, the size of the lines fetched starts to get significantly higher in 12.1 than in 11.2:

JOIN #	QV 11.2	QV 12.1
1	779,040	779,040
2	865,471	865,471
3	865,471	865,471
4	800,132	912,459
5	801,224	959,447
6	802,316	1,006,435
7	803,408	1,053,423
8	804,500	1,100,411
9	805,592	1,147,399
10	806,684	1,194,387
11	807,776	1,241,375
12	808,868	1,288,363

The result of these joins is a table that is 5,517,283 DISTINCT rows of data that is LEFT JOINed to another table using a Unique Row Identifier. This same process in QV 11.2 only joins 907,148 rows of data.

Fix:

I don't know when the series of LEFT JOINs 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.

[pieterkup]

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.

I hope this helps

[ciaran_mcgowan]

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):

Child_ID - Parent_ID - Grandparent_ID1 - Grandparent_ID2 - Grandparent_ID3 - Grandparent_ID4 - Grandparent_ID5 - Grandparent_ID6 - Grandparent_ID7 AS New_Child_ID

Parent_ID - Grandparent_ID1 - Grandparent_ID2 - Grandparent_ID3 - Grandparent_ID4 - Grandparent_ID5 - Grandparent_ID6 - Grandparent_ID7 - Grandparent_ID78 AS New_Parent_ID

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.

[Anonymous]

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.

[rmoraes_mp_rs]

Mr. Erno, 

I have the same problem. And, when I run with version desktop , the proccess is more fast to comparate with version server..

The "slow" started after upgrade 12. 

Something answer about your ticket?

*My english is very bad..sory.. talk from Brazil

[zhadrakas]

Any news about this?

I've upgraded from QV 11 to 12.3.

For me it only affects mapping Load Statements.

Normal loads are fetching same rows. But Mapping Loads are 5 times slower then before.