Improving Performance of QlikView Server and Documents

[Sonja_Bauernfeind]

The performance of a QlikView document depends on many factors including:

1. Data Model
2. GUI (The objects - charts etc. - of the Qlikview document)
3. Hardware

The first step is to find out which one(s) are causing performance issues. 

1) Data model

For a well performing data model it is preferred to have one large “fact table” and to use a star schema. If an application contains several large "fact" tables then performance will be slower than necessary and the application can be optimized by changing the model.

Performance can be improved by splitting applications - instead of one application with several fact tables, use separate applications with one fact table.

2) GUI

These are common reasons for bad performance in the GUI:

1. Using key fields in chart dimensions and expressions
   Look at how charts are defined and eliminate key fields (indicated with key symbol in field lists). Usage of key fields can cause heavy calculation overhead, and also lead to unexpected calculation results. 
   
   
2. Calculated dimensions
   Look at how the charts are defined and if they contain any dynamic dimensions. It may be possible to calculate these in the script instead of calculating them on every selection.
   
   
3. Sorting of large amount of values:
   
   For example in a table box, there may be sorting on every column because sorting was turned on by default. That could mean that large amounts of values are sorted several times - on the first column, then on the second, etc.
   
   To find out if the problem is in the GUI or Data Model:
   
   
   1. Create a new blank document
   2. Binary load the data from the original document (Binary c:\original_document.qvw;)
   3. Create simple list boxes for relevant fields

If this document, where the GUI has been nearly eliminated, also performs slow, then the slowness is not due to the GUI.

Analyzing the GUI
If one suspects performance issues being related to one or many objects in the GUI, it could sometimes be difficult to find which object to investigate further (this is especially true in a QlikView document containing hundreds of various objects). In this case a memory statistics analysis could performed to eliminate objects causing performance issues. To do a memory statistical analysis do the following:

1. Open the QlikView document you want to analyze in QlikView Developer
2. Click through the sheet(s) and activate any object(s) like you would do when normally using the document
3. Go to Settings - Document Properties - General tab
4. Press the button Memory Statistics... This will allow you to export a file containing statistical information about each object within the application (note: statistics will only be captured for active/calculated objects, hence it's important to click around as described above to make QlikView perform the required calculations).

Once the *.mem file has been saved, it could be read back into a new QlikView document for further analysis of memory usage and CPU usage (calculation time) for each object (see attached example).

3) Hardware

The performance will depend on the hardware and BIOS settings, for example Hyper-Threading, Power Management and Node Interleaving. Recommendations can be found here: BIOS and Hardware Settings For Best Performance On QlikView Server And QlikView Publisher.

To know if the hardware is a factor, it can be useful to run the application on another machine, even a laptop if it has enough memory, to see if the behaviour is significantly different. If the behavior is better on the other machine then it is worth looking over the hardware settings to see if anything can be improved.
 
Note:

Professional services have a health check service that can do a health check of a document and make suggestions for improvements. They have a similar service similar for hardware health checks. So it may be worth contacting your account manager for further suggestions.

Environment:

QlikView