Well that’s a wordy title isn’t it?
I’m in the midst of writing a new QlikView Document Optimization course to be delivered at Q-On Training . This work has reminded me of a not-so-obvious issue I sometimes see in Performance Tuning engagements with customers.
You might be thinking I’m going to write today about how heavy a calculation can be as a result of the cartesian product of disconnected fields in an expression . No…that’s not what I’m thinking of.
What I’m thinking about today is the impacts of selecting a field that is not used by any expression on the sheet. For example, a Currency listbox may be present on the sheet. The Currency field is not connected (“Data Island”) to other tables in the model . Some, or none, of the objects on this sheet may reference that Currency field.
What happens when you click a Currency value? Everything on the sheet gets recalculated. E v e ry t h i n g. Whether it uses Currency or not. Why? Because the data has changed.
Since the data used in my chart has not changed, the results will be fetched from cache, right? Probably not. Let’s look at an example:
The current selection in Currency is “USD”. The chart has been calculated and the results stored in cache, available for speedy retrieval if the same expression is calculated over the same set of data.
Select “EUR” in Currency. The cached result will not be used even though no change has been made in the data used by the chart. Cache evaluation considers the entire data model, not just data referenced by the object. If you now select “USD”, the previously cached result will be used.
If your application is large enough that you consider this behavior to be a problem, a leaner alternative for the Currency example is to use a variable. When a variable changes, only objects that reference the variable get recalculated. Another great alternative is to put the Currency listbox in a different Alternate State.
Let’s look at another case, the idea of a “universal listbox” that has been published by a several authors. I think it’s a very cool idea and I use it myself for data exploration. The common idea is that you let the user pick any field and then make selections in that field. This can be built using the system fields $Table and $Field.
Let’s consider the app is idle and all the sheet objects are occupying what I’ll term “relative cache slot #1” – the first cache entry for each object. Now we’ll use the universal listbox.
1. Select a $Table value. Everything is recalculated, consuming CPU resources and the results are stored in “cache slot #2”.
2. Select a $Field value, “ProductGroup”. Everything is recalculated, consuming CPU resources and the results are stored in “cache slot #3”.
3. Make some selections in “ProductGroup”. Everything is recalculated, consuming CPU resources and the results are stored in “cache slot #4”. This result, with the new data, is what the user is interested in.
We have used three times the CPU and cache resources to achieve the end result. A leaner alternative would be to place the universal listbox on it’s own sheet, thus avoiding extra recalculations. You can move back and forth with buttons to make it feel like it’s integrated with each sheet.
I don’t mean to discourage the use of these data island techniques. They can be incredibly useful to the #1 goal — getting accurate information to your business in a timely and cost efficient manner. However, when you are dealing a specific performance concern in larger apps this is an area you may want to examine and optimize.
Watch the Q-On Training site or subscribe to our Q-On newsletter to be notified when the new Performance course is available. For more performance related discussions and tips, join me at the Masters Summit for QlikView Sept 23-25 NYC or 29 Sep-1 Oct Copenhagen.