2 Replies Latest reply: Nov 7, 2018 11:32 AM by ramon arcusa RSS

    Caching variables

    ramon arcusa

      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!

        • Re: Caching variables
          Dilip Ranjith

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

          The QlikView Cache

           

          rwunderlich your thoughts?

          • Re: Caching variables
            Rob Wunderlich

            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/InterRecordFunctions/column.h… 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

              • Re: Caching variables
                Mike Wang

                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

                  • Re: Caching variables
                    Rob Wunderlich

                    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

                    • Re: Caching variables
                      ramon arcusa

                      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!