As you may have heard, 2 Business Intelligence consultants joined our team at the beginning of May. Perfect timing in parallel with their training and Qlik coaching sessions to attend the Qlik World 2021 webinar…. And share with you some interesting topics!

Among the subjects studied with care, we attended this one: “Hacking Qlik Application performance for more and faster data” which largely inspired the contents of this article.

We have therefore put together a set of tips that will allow you to improve the performance of your Qlik Sense applications and the loading of your data. The solutions offered are easy to implement when developing an app, or even when modifying an existing app.

In this article, we focus on best practices for:

• Data loading (Data Load Editor)
• Development in apps

Please note that these are tips and that depending on the case, other solutions may be more effective.

Why worry about performance? 

Improving the performance of your apps will allow different users of your Qlik solutions to benefit from a better experience at all levels. Here’s how performance can affect different types of users:

• End Users: If loading data is faster, it can be done more often. The end user will therefore be able to make better decisions with up-to-date data. An application with an optimized data model will allow for stronger connections between different data and faster navigation which will make the experience more pleasant for users.
• Developers: For a developer, loading data quickly or having optimal response time on apps allows them to make changes and test those changes faster.
• Administrator: An optimized architecture will reduce the load on server resource usage. If Qlik takes less time to load QVDs, there is less chance of conflicts and errored jobs.

To find out which application is taking too long to reload, you can use the monitoring apps.

I. Improve the performance of your apps and data loading at the Data Load Editor level

1)    Limit the amount of loaded data

Having less data to load logically improves performance. Here are three ideas for limiting the amount of data loaded:

• Incremental loading: load only new data or updated data. Old data or unmodified data can be kept in QVDs that we can call upon after loading new data.
• Databases can sometimes include a lot of information.It may be wise to filter on the relevant data based on an unique key and thus load less data.
• Only load data that will be used within the application.

2)    Optimize the loading of QVDs

For technical reasons that we will not detail here, not modifying the data when loading a QVD makes the data loading very efficient.

One action that can be performed on loading a QVD without degrading performance is to use a “where exists ()“.

As a reminder, “where exists ()” allows you to load only data for which the values of one of the columns has already been imported into another table. For example, if I have a product table and an invoice table, thanks to “where exists ()”, I can load the product data for which an invoice exists. So I only import the products that are used in the app and not all the products in the source database. Attention point: the “where exists ()” is not optimal in all cases. For large volumes, choose “left keep”.

It is possible to see if a QVD is optimized in the Data load progress :

Here are some examples of unoptimized QVD loads (in pink) made more efficient through where Exists () :

3)     Limit the number of calls to data sources

Using data sources takes time and resources. It is best to load all the necessary data once and consequently connect to the source database only once.

If this data must be called again later in the code script, refer to it using a Resident. This will load the data from an already existing table rather than calling the database again.

4)    Limit smart search data

Rather than disabling the search index, we recommend that you:

• Activate it
• Exclude certain fields on which the end user will not search.

As a reminder, it often happens that developers disable the search index because it is an easy way to save time when loading data. For example, you can exclude IDs from smart search, which are often values that the end user does not know.

In the following example we exclude anything that contains ‘Amount’ and ends with: ID ’ :

5)    Prepare the data

In order to optimize the use of your application, it is interesting to prepare as much data as possible upstream and transform it to meet the functional needs of the users in the script. This will avoid having to do calculations in the apps and therefore improve their performance.

We suggest the following solutions:

• You can use “group by” on the data load so that you don’t have to do it in the apps.
• If possible, also resolve complicated relationships before or during data loading so that they do not have to be done in the app.
• If two columns always go together, it can be interesting to directly combine them during loading.

For example Employee Name and Employee ID could always be displayed together in the app. It wouldn’t make sense to have to concatenate these two pieces of information each time in the app.

• If you know that some data will always stay the same, you might as well prepare it in the Data Load Editor. For example, it may be interesting to have already managed the format of dates, numbers or even temporal information in the Data Load Editor.

6)    Using the autonumber

Create unique and simple keys using the autonumber command in the load script. When using the app, this avoids the use of heavy and long keys and therefore improves performance.

We draw your attention to three points:

1. We are talking here about the AUTONUMBER command in the script and not about the autonumber () function.
2. Autonumber will slow down the load of the data but will make apps more efficient.It is therefore important to use this with keeping in mind the consequences of using it.
3. By realizing the autonumber, it is no longer possible to do an incremental load easily.

7)    Use concatenates

Using concatenates will not save time when loading data. However, the use of apps will be optimized if it is used to chain small tables together.

As a reminder, the concatenate allows you to import all your data into a single table rather than multiple tables.

8)    Joins

We recommend to avoid joining large tables in the Data Load Editor. When using the app, you risk losing performance because it will be more complex for Qlik to find the data.

Examples :

Here are other examples of optimized loads :

II. Improve the performance of your apps when developing your apps

1)    To exclude from his vocabulary

Using variable formulas can be very helpful. These are formulas which vary depending on the user’s choice. For example, he can choose to display either the sum of the quantities sold, or the average unit price, or the percentage of margin, etc.

To do this, is it not recommended to use IF or Pick + Match formulas as follows:

• If (Condition = 1, sum (quantity)
• If (Condition = 2, Avg (UnitPrice)
• If (Condition = 3, sum (Margin) / Sum (Turnover) )))
• Pick (Match (Condition, 1,2,3), sum (quantity), Avg (UnitPrice), sum (Marge) / Sum (Revenue))

This is because the Qlik Sense engine calculates each of the alternatives, but only displays one. Depending on the volume of the application, the use of IF or Pick + Match can therefore negatively impact the responsiveness of the application.

Here’s what Qlik has to offer about it: Create a table called “Island”(detached from the model) and call each formula via a selection made on the name of the formula :

2)   Limit the data displayed

Any unnecessary values or columns should be eliminated. Displaying each piece of information will decrease your performance. So limit yourself to the minimum necessary.

3)    Managing amount of distinct data

We also recommend that you reduce the number of distinct values. This can be done by rounding off for example, or by subtracting seconds and thousandths of a second from time values.

4)    Display graphs under certain conditions

As you know, Qlik offers the possibility to load an object only under certain conditions. We recommend that you use this feature when there is a lot of potential data to display. For example, you can display a graph only if the selections made result in less than 100,000 items of data to be calculated.

Useful links : 

• Performance analysis:

We also wrote an article on analyzing the RAM memory used on Qlik sense SaaS. Dont hesitate to take a look at it here.

Qlik also offers a set of best practices in this article.

• Video :

The following vidéo will also explain how some features perform less well than others.

Here is the Qlik article linked to the video.

Need Support ?

For more advice on the performance of Qlik tools, contact your Qlik partner or consultant.

 

Laetitia, Sales & Marketing manager

 Stay informed of the various WeQan news by following us on LinkedIn et Facebook.