Reusing the QlikView cache (or not) across the front end of a QVW
by Mike Steedle, on Apr 23, 2014 9:21:08 PM
In a recent post on the QlikView Design Blog (The QlikView Cache), Henric Cronström stated:
The cache is global. It is used for all users and all documents. A cache entry does not belong to one specific document or one user only. So, if a user makes a selection that another user already has made, the cache is used. And if you have the same data in two different apps, one single cache entry can be used for both documents.
Based on my experience, I said:
I would be surprised to find out how the same cache entry can be used across documents, considering that QlikView doesn't even seem to reuse the same expression grouped by the same dimension in different chart types within one application. Ex. a straight table with Sum(Sales) by Customer and a bar chart with Sum(Sales) by Customer are calculated independently within an application. At least, this appeared to be the case when I tested this in an earlier version of 11.0.
It should use the cache across documents and my experience is that it does.
If you have a case where the same combination of dimension and expression doesn't use the cache, there is probably a reason, e.g. that the expression (or a calculated dimension) is written in different ways in the two places. Either that, or you have found a bug. Because it should re-use the cache entry...
It's been a few years, so I think it's time for a retest in 11.2 SR5. Here is what I used to generate sample data:
LOAD 'C' & Left(RowNo(),1) as Country,
Left(RowNo(),4) as OrderID,
Round(Rand()*500) as Sales,
Round(Rand()*30) as UnitCost,
Round(Rand()*10) as Quantity
(My laptop smokes, so you may want to generate fewer rows for yours.)
This is my chart definition. All of the charts were created by copying and pasting the first Straight Table, so there is no chance of variance in the writing of expressions.
- Dimension: Country
- Expression 1 (Avg Order Sales): Avg(Aggr(Sum(Sales),OrderID))
- Expression 2 (Avg Order Sales Index): Avg(Aggr(Sum(Sales),OrderID)) / Avg(TOTAL Aggr(Sum(Sales),OrderID))
Sheets on front end:
- Landing page (empty)
- Straight table with one dimension and two expressions
- Copy of Straight table (unlinked)
- Copy of Straight table, but changed to a combo chart
- Copy of Straight table, with the second expression disabled
The test was to click through the sheets, in sequence, and check the calculation times of the object on each sheet. I did this three times and averaged the results.
Two conclusions that could be surprising about how the cache is working:
- A copy of a chart calculates instantaneously with the original chart cached, while copying the chart and changing only the chart type takes significantly longer, although not as long as the original. In other words, cached results from one chart type do not appear to create the same, instantaneous results in other chart types, despite having the same measures and dimensions.
- Even I was surprised to find that a copy of a chart with one of the expressions disabled did not reuse the cache from the original chart.
This information, taken in conjunction with the fact that QlikView is not good at recognizing two expressions really mean the same thing, leads me to believe that the cache does little to improve performance across objects within an application, only making a meaningful difference when recalculating a chart based on its own cache.
Again, I am doubtful QlikView is currently finding cache efficiencies across applications, based on these kinds of results--and I can see other areas where optimization work would be better spent. Namely, if they were able to improve cache reuse within an application and better recognize when expressions are written differently but actually the same, customers could perhaps see meaningful gains in performance with no effort on their parts beyond upgrading the server software.