• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

Performance Tuning in Qlikview

Performance Tuning

Qlikview application can be optimized so that it executes more rapidly, or is capable of operating with less memory storage or use fewer resources. There are fewer ideas and techniques that can be done to optimize the Qlikview Application. Optimizing the Qlikview application can be done at

Server Level

Scripting level

Design level.

Optimizing Qlikview Application at Server Level:

Data Compression Technique:

In general, Qlikview consumes more memory while reloading and while opening the qlikview file. This can be rectified by saving the file with lower compression.

Breaking single large QVW file into multiple smaller QVW:

If a Qlikview application is large in size, obviously it will consume more memory during opening the application. Splitting a single large qlikview documents into several separate documents will help in solving the issue.

Load Balancer: Server load will be very high if concurrent users using Qlikview web server is high. It can be overcome by using network load balancer(Servers with Windows Advanced Server, IIS And QlikWebServer)

Optimizing Qlikview Application at Scripting Level:

Removing Synthetic key:

In data modelling, synthetic keys might formed if there two or more tables have common columns exist. Synthetic keys will greatly impact the performance and it is better to avoid or removing it. This can be done by removing the unnecessary links and join the tables explicitly in the script.

In some cases, reloading qlikview might take long time to load. The best practice to overcome is using a Binary Load of static historical data.

Dropping Temporary Tables and fields:

The temporary tables are used in qlikview scripting mostly for doing calculations (Resident Load). These temporary tables can be dropped once when their purpose is achieved.

Handling Expressions/Calculations:

Complex calculation within a dimension or expression or in any of the Qlikview objects will give poor performance and it will be better to use the complex calculations within the script of the QVW.

Avoiding resource heavy expressions or calculations greatly hinder the performance of the qlikview application. Replacing with simpler calculations will help and its a good practice too in scripting.

Example: Count(Distinct,’ Fieldname’)

Instead of the above expression, replace the count() with sum() and the distinct qualifier by assigning the value ‘1’ to each distinct occurrence as it is read in the script.

Use of Auto number Function:

Avoid using Complex composite keys(when to remove synthetic keys), and instead use the autonumber() function to generate a sequence which uses compact memory.

Use Star schema instead of snow flake schema.

Optimizing Qlikview at Design (Layout) Level:

Monitoring Memory Consumption at Object Level:

Memory utilized by the qlikview objects and the calculations time can be monitored from the document properties. This will helps to identify which object is consuming more memory and finding the reason for delay in loading time of the Qlikview application.

Minimized Chart Vs Maximized Chart:

Minimized chart objects will consume less memory comparing to the maximized one, hence use of autominimize option will be a good practice in this case.

If the chart is too large, then implementing forced selection by the user will minimize the chart calculation time.

Showing frequencies in listbox can be avoided if not necessary.

During sorting, it is recommended to sort numerical fields numerically than alphabetically.

Use Set Analysis instead of ‘If’ statements.

Avoid usage of Macros and unnecessary Triggers.

Information Density: Information Density is the number of records that have values (i.e. not NULL) in this field as compared to the total number of records in the table.

Subset ratio: Subset ratio is the number of distinct values of this field found in this table as compared to the total number of distinct values of this field (that is other tables as well).

Circular Loops: It is undesirable to have multiple common keys across multiple tables in a QlikView data structure. This may cause QlikView to use Circular references to generate the connections in the data structure. Circular references are generally resource heavy and may slow down calculations and, in extreme cases, overload an application.

-Comment the fields in load script

-Rename the fields in load script

-Rename the fields using UNQUALIFY operator.

Performance Tuning in Qlikview

Error handling

ErrorMode

ErrorMode is the first statement to be used in begging of the script

-Errormode = 0 – Will ignore all script errors and continue refreshing application

-ErrorMode = 1 – Will halt the script execution and prompt a script error message dialog box

-ErrorMode = 2 – Will stop execution where there is a error in table load and shows as execution completed. Once click on close we will get a popup saying “script execution failed”.

Syntax :

SET ErrorMode = 0;

Script Error

-Returns the error code of the last executed script statement

-This will be reset to 0 after each successfully executed script statement.

-If an error occurs it will be set to an internal QlikView error code. Error codes are dual values with a numeric and a text component.

Below are the different error codes

Error             Code Error
0                     No error
1                      General error
2                     Syntax error
3                     General ODBC error
4                     General OLE DB error
5                     General custom database error
6                     General XML error
7                     General HTML error
8                     File not found
9                      Database not found
10                    Table not found
11                     Field not found
12                    File has wrong format

13                     BIFF error
14                     BIFF error encrypted
15                     BIFF error unsupported version
16                     Semantic error

ScriptErrorCount

-Returns the total number of statements that have caused errors during the current script execution.

-This variable is always reset to 0 at the start of script execution.

Summary
Review Date
Reviewed Item
Performance Tuning in Qlikview
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Performance Tuning in Qlikview"

Leave a Message

Your email address will not be published. Required fields are marked *

Site Disclaimer, Copyright © 2016 - All Rights Reserved.