In this post we will give you some tips to improve the performance in QlikView while developing your application.
These improvements can be done at different levels:
- Script
- User Interface
- Server
Find below these little tips that can make a big difference in the user experience of your app:
- Use numbers as key values instead of text
When linking between tables using key fields, it is recommendable to use numbers as key values instead of text. ‘Autonumber’ is a useful function to use in this case.
Autonumber (expression [, AutoID])
Returns a unique integer value for each distinct evaluated value of expression encountered during the script execution.
Example:
| Ciudad | Autonumber (City) |
|---|---|
| Spain | 1 |
| EEUU | 2 |
| Germany | 3 |
| EEUU | 2 |
| Germany | 3 |
| France | 4 |
- If sorting is not needed, turn it off
Especially if fields are evaluated as text they will be sorted as text, which is the slowest sort operation.
If it is necessary sorting text values, you can create in the script a temporary table with the text sorted. After reloaded, you can sort this field in any object by load order, which it is quickly because it does not evaluate the fields.
- Avoiding resource heavy expressions
Replacing them with simpler calculations will help. It is a good practice both in scripting and in objects.
Some of these expressions are:
- Count (distinct [FieldName])
- If (Condition (Text), …)
- Sum (If (Condition, [FieldName])…)
- If (Condition, Sum ([FieldName])…)
Possible solutions:
The distinct qualification is costly especially if the field is a string. A useful technique is to assign a value to each new value as the field is read using the ‘Autonumber’ function.
Create flag fields for those analysis or filters you want to use in the expressions. It is a useful way to improve performance.
Try to use Set Analysis instead of ‘IF functions’ in objects expressions.
- Remove Synthetic Keys
Synthetic keys are formed in tables that share more than one field. Synthetic keys are anonymous fields that represent all occurring combinations of these shared fields.
When the number of composite keys increases, depending on data amounts, table structure and other factors, QlikView may end up using excessive amount of time and/or memory Synthetic, so it is better to remove them.
In this case above, SalesOrderNumber and SalesOrderLineNumber are synthetic keys:
Possible solutions: rename fields, remove conflicting fields from one of the two tables, concatenate the tables or create a complex key with the concatenation of all common fields (remember the use of autonumber function for the key values).
- Use Star Schema as Data Models
Star schemas are generally the best solution for fast and flexible QlikView applications.
- Drop any unnecessary fields
An unnecessary field is one which isn’t currently being used. The temporary tables are used in QlikView scripting mostly for doing calculations. These temporary tables should be dropped once when their purpose is achieved.
Example:
- Limit the number of objects
Particularly on the same sheet, but overall as well. Memory utilized by the QlikView objects and the calculations time can be monitored from ‘Memory Statistics’ in the document properties.
On the other hand it is good to know that expressions in charts and tables are only calculated when the object is visible and data changes.
- Limit the number of text values in a field
Break up long strings into separate component fields rather than using one. For example, if you have the complete address in a field you can separate it in different fields like street, city, zip code, etc.
- Restrict the number of user sessions of one document in QlikView Server
This might be used when we have a very large document in our server.
These tips are not universal, each application and requirements are different. But if you are finding performance problems in your application, this post will be a very good start point.
Do want to know more about QlikView? Don´t hesitate to enroll in our courses.