Performance issues with large pivot table / running into memory limit
Hi everyone,
I’m currently facing significant performance issues in a Qlik Sense app and would really appreciate any insights or suggestions.
After multiple optimization efforts to reduce data volume, the app size is still around 2.1 GB. The main problem lies in a specific sheet that contains a pivot table. The underlying dataset for this table currently has approximately 36 million rows.
Due to stakeholder requirements, the table needs to be highly flexible:
Around 40 dimensions are available, controlled via show conditions and selectable through a selection pane
Users must be able to dynamically rearrange dimensions within the pivot table
Additional filters can be applied on top of this setup
Because of this flexibility requirement, we are using the standard pivot table object, as the alternative pivot table does not support this level of interactivity.
We have already removed most of the formatting logic from the dimensions to improve performance. While this helped somewhat, we are still experiencing:
Overall slow performance of the pivot table
Frequent memory limit errors, which are the primary concern
What’s particularly confusing is that this app was migrated from QlikView. In the original QlikView version, even more data is loaded, yet we never encountered memory limit issues there.
At this point, I’m looking for any ideas or best practices to improve performance, especially regarding the large pivot table with many conditional dimensions.
Please feel free to ask any follow up questions if more context is needed. Thanks in advance for any help!
Hey Robert, What did you mean with "is all exactly the same?" ? On the data model: The data model is clean and should be fine. We do not have any synth keys, nor any loops or anything of that kind. We do have a lot of tables and the main table with currently 36 million rows, but the model is optimized in its current form.
On the data size: We managed to decrease the app size from 3.2GB to 2.1 GB, but sadly without any noticable performance improvements in the pivot table in question. The mentioned 36 million row table is by far the largest. When running the app performance evaluation we see that the sheet with the pivot table in question is noticably and unsurprisingly, the heaviest.
