Application Performance Optimization

Introduction

With small or medium sized QlikView applications you generally don’t have to worry too much about the design of the application in terms of performance. As the amount of data grows, both time and memory constraints may become very evident if the application is poorly designed. It is possible that some simple design alterations improve performance substantially. This appendix points out a few common pit falls and suggests remedies for them.

In general performance is improved by moving the “problem“ from application objects to the script driven database. This is often a trade off situation. Response time is enhanced and ad hoc capability is diminished. The recommendations below should not be seen as universally beneficial. Use them when they improve the general state of the application or when they make that little bit of difference that makes or breaks.

The following is a list of examples of applied methods for the handling of the problems above. They are meant to illustrate the problem and point at useful QlikView functionality. It is not possible to give a general recommendation as to which method is the best, but the order of the examples is an indication.

If ( Condition(Text),....)

If clauses involving text comparisons is generally expensive. Solutions can be to map text to numbers, for example by using autonumber (see examples in the previous section) and/or do the test in the script.

The testing of text strings is slower than numeric testing. Consider the expression:

If (Alfa= ‘ABC’, ‘ABC’, left (Alfa, 2))

The test could be done directly in the script without losing any flexibility.

Load

*,

If (Alfa = ‘ABC’, 1, 0) as Flag

resident table_1 ;

The expression becomes:

If ( Flag = 1,’ABC’, left (Alfa, 2))

and the test is much simpler.

Sum ( If (Condition, ‘FieldName’…))

Here the aggregation is independent of the table dimensions and the result is distributed over the dimensions of the table. The problem can be treated either by doing the test in the script and aggregating in the table or by doing the whole operation in the script. There are numerous techniques for this, such as interval match, group by, peek, if....then....else.

This case involves two steps namely the testing of “Condition“ and the aggregation of the result. If we take the previous example and add the aggregation

Sum ( If (Alfa= ‘ABC’, Num*1.25 , Num) )

Load

*,

If (Alfa = ‘ABC’, 1, 0) as Flag

resident table_1 ;

The expression becomes

Sum ( If ( Flag = 1, Num* 1.25 , Num ) )

The aggregation can also be done directly in the script as follows:

table_2:

Load

*,

If (Alfa = ‘ABC’, 1, 0) as Flag

resident table_1 ;

table_3:

Load

Alfa,

If ( Flag = 1, Num* 1.25 , Num ) as NewNum

resident table_2 ;

 

table_4:

Load

Alfa,

Sum( NewNum ) as SumNum

resident table_3

group by Alfa ;

If ( Condition, Sum(‘FieldName’)..)

This construction is included here only to emphasize the difference to the previous case. This aggregation is completely contextual and generally speaking does not cause performance problems.

If ( Condition1, Sum(‘FieldName’), If (Condition2, Sum(‘FieldName’)……..

The logic of nested If...then else... is conceptually easy but can often become troublesome to administer. We have seen cases with hundreds of nesting levels. This is both memory as well as CPU intensive. The “Conditions“ can often be replaced by transforming them. A typical example is aggregating quantity*price where price is variable. This can be handled by “extended interval match“. If two conditions, e.g. “A AND B” are to be satisfied the test might be replaced by a condition “C“.

Example:

sum((GAC12_STD_COST * GAC15_EXCHANGE_RATE) * GIV24_DISP_QTY)

Replaces

Sum(

If((GAC12_EFCT_DT<= GIV23_REJ_DT and

GAC12_EXPIRE_DT>GIV23_REJ_DT) and

(GAC15_EFCT_DT<= GIV23_REJ_DT and GAC15_EXPIRE_DT>GIV23_REJ_DT),

GAC12_STD_COST * GAC15_EXCHANGE_RATE) * GIV24_DISP_QTY,

Null()))

and

Sum(

If(GAC12_EFCT_DT<= GIV23_REJ_DT,

If(GAC12_EXPIRE_DT>GIV23_REJ_DT,

If(GAC15_EFCT_DT<= GIV23_REJ_DT,

If(GAC15_EXPIRE_DT>GIV23_REJ_DT,

(GAC12_STD_COST * GAC15_EXCHANGE_RATE) * GIV24_DISP_QTY,

Null())))))

by reading the fields GAC12_STD_COST and GAC15_EXCHANGE_RATE as slowly changing dimensions.

Using the extended intervalmatch syntax to resolve slowly changing dimension problems

Sorting Text

QlikView automatically evaluates if a Field is to be treated as numeric, text or general. Fields evaluated as text will be sorted as text which is the slowest sort operation. This can be replaced manually to sort by load order. If sorting of list boxes etc is not needed, turn it off.

QlikView sorts strings of mixed characters and numbers in alphanumeric order. That is, numbers are sorted in value order while non-numbers are sorted in ASCII order, as opposed to tradtional ASCII-only sort order. Example:

Dynamic Captions and Text Objects

Dynamically calculated expressions can be entered almost anywhere where you can enter text. The resources required for evaluation of an expression is however dependent on its environment. Expressions in charts and tables that are defined in the expressions dialog are only calculated when the object is visible and data changes. They are e.g. not calculated when the object is minimized.

On the other hand, if the object title is calculated this calculation is performed every time any change occurs. There are also numerous ways of defining show conditions, calculation conditions etc. These tests will also be performed at all times.

Some expressions are more expensive than others and become even more expensive the more frequently they have to be evaluated. The introduction of asynchronous calculation has shifted the behavior and maybe these effects have become more noticeable in your applications.

The time functions e.g. Now() and Today() will be evaluated whenever a recalculation is required. Especially the Now() function can become quite costly since it causes a recalculation of the application every second.

For example:

If ( ReloadTime()+3>Now(), 'Old Data', 'New Data')

Here one might consider

If ( ReloadTime()+3>Today(), 'Old Data', 'New Data')

As a simple test, put the expressions into text boxes. Then try sizing the text box with Now() in it.

Macro Triggers (“on change“)

Macros can be set to be triggered by almost any event taking place in the application. Beware of cascading or recursive events where one event triggers the next which in turn.