Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ramonarcusa36
Contributor III
Contributor III

Caching variables

Hey guys.

I have a simple question about performance and variable caching. Let's suppose I have these two expressions:

Expr1: Sum(Units)

Expr2: Sum(Dollars)

Now, let's suppose I want to calculate a third expressión: Price.

Expr3: Sum(Dollars) / Sum(Units)

As far as I'm concerned, if I want optimum performance I would write the third expression like this:

Expr3: Column(2) / Column(1)

And then, QlikView would just use the previously calculated expressions (Expr1 and Expr2), optimizing performance (labeled aliases would also do the trick).

But what happens if I use variables? Let's suppose this:

Expr1: $(vAmountOfUnits)

Expr2: $(vDollars)

Expr3: $(vDollars) / $(vAmountOfUnits)


In the third expression, would QlikView use the previously cached $(vAmountOfUnits) and $(vDollars)? Or since it's a totally different expression (even if it contains parts of previous ones) it would have to calculate units and dollars again? (Obviously, I know I could go with the Column(x) approach here too, but my real situation is far more complex than this; this is just a very simple example to find out how QlikView deals with variables and caches).


Thanks in advance!

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

In my testing I find that there is no performance benefit to using the column(n) / column(n) method.  Although the doc https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/InterReco... states "Column() returns the value found in the column " I believe it actually reuses the expression text found in the column, not the calculated value.  The benefit is in maintainabilty of the chart, not performance.  I'd be happy to hear arguments and examples to the contrary.

With regards to the "$(vDollars) / $(vAmountOfUnits)" variable question.  To my knowledge and observations, QV does not cache expression results or fragments, it caches full chart results.  Therefore "$(vDollars)" being calculated earlier in the chart will not be reused in a "$(vDollars) / $(vAmountOfUnits)" expression. it is an entirely new expression from the standpoint of cache. 


Further, my experience is that "$(vDollars) / $(vAmountOfUnits)" will not match to the cached version of "Sum(Dollars) / Sum(Units)" even though after DSE they are the same expression.


I find the same observations with the Qlik Sense client. 


Interestingly, when using the Qlik Sense API "createSessionObject()" the variable version does seem to be equivalent to to the text version. it appears that cache identity when using the API is done post DSE.


I'll admit to some mysteries here and welcome comments from those who have more knowledge of the internals or have measured results contrary to mine.


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

View solution in original post

6 Replies
dplr-rn
Partner - Master III
Partner - Master III

from below post, i believe it does as long as same dimensions are used.

The QlikView Cache

rwunderlich‌ your thoughts?

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

In my testing I find that there is no performance benefit to using the column(n) / column(n) method.  Although the doc https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/InterReco... states "Column() returns the value found in the column " I believe it actually reuses the expression text found in the column, not the calculated value.  The benefit is in maintainabilty of the chart, not performance.  I'd be happy to hear arguments and examples to the contrary.

With regards to the "$(vDollars) / $(vAmountOfUnits)" variable question.  To my knowledge and observations, QV does not cache expression results or fragments, it caches full chart results.  Therefore "$(vDollars)" being calculated earlier in the chart will not be reused in a "$(vDollars) / $(vAmountOfUnits)" expression. it is an entirely new expression from the standpoint of cache. 


Further, my experience is that "$(vDollars) / $(vAmountOfUnits)" will not match to the cached version of "Sum(Dollars) / Sum(Units)" even though after DSE they are the same expression.


I find the same observations with the Qlik Sense client. 


Interestingly, when using the Qlik Sense API "createSessionObject()" the variable version does seem to be equivalent to to the text version. it appears that cache identity when using the API is done post DSE.


I'll admit to some mysteries here and welcome comments from those who have more knowledge of the internals or have measured results contrary to mine.


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

mikevwang
Contributor III
Contributor III

I just tried the Column() expression and I am pretty certain it uses the value found in the column rather than reusing the expression text. Here is what I tried:

Test: LOAD 1 as A, 2 as B AutoGenerate 1;

Straight Table Chart:

Dimension: A

Expressions...

Exp: =SUM(AGGR(SUM(B),A))+SUM(AGGR(SUM(B),A))+....+SUM(AGGR(SUM(B),A))

Exp2: = same as Exp

Col: =Column(1)

The +SUM(AGGR(SUM(B),A)) was repeated 3500 times to create an expression that is difficult to cache. (the SUM(AGGR(SUM(B),A)) are cached individually, but 3500 cache pulls need to be done separately.)

Calc time with Exp and Exp2: 9703

Calc time with Exp and Col: 2100

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Mike,

Can you confirm that you repeated your test by calculating each chart as the first calculation?  ie Chart A followed by Chart B, then Chart B followed by Chart A?

-Rob

mikevwang
Contributor III
Contributor III

Yes, I have tried in reverse order and also tried removing impact of any carryover caching by saving on blank sheet and then restarting QV completely before measuring each change.

ramonarcusa36
Contributor III
Contributor III
Author

Thank you all for your very valuable inputs.

I could test what Mike Wang said, and it's true! If I have two similar expressions, the object calculation time is twice the time as if I just had one expression. Even if the expression is exactly the same (copied - pasted). If second expression is just a reference to the first one (Column(1)), time it's the same as if I just had one expression.

I understand that storing an expression in a variable doesn't make its calculation to be cached for further uses of it as a part of other larger expressions. So my question gets totally asked.


I'll get a mixed approach: I'll use variables to encapsulate expressions but I'll keep using Column(x) to prevent QlikView from calculated same thing twice.


Thanks again!