Improving QlikView Performance

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:

CiudadAutonumber (City)
Spain1
EEUU2
Germany3
EEUU2
Germany3
France4
  • 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.

Deja un comentario