How to move the all the Calculations and Expressions to the Script...?
Hi Experts,
Our Current Application size is 2.9 GB, Special Project for me first time am dealing with large Size file , We are facing lot of problems while using it , so for our project optimization is mandatory.it is taking much time to respond for Selections.
Qvw Size : 2.9 GB
Server Ram Size : 64 GB
Application details : we are using almost all type of charts in different containers , and the Expressions & calculations are also very lengthy, set analysis and along with if conditions to do number format. and different variables , field triggers...
we are planning to move some Expressions And Calculations to the Script Part, is it good decision are Unknowingly we are going to make it Complex..? or any other Optimization technique which gives better performance...?
Please Suggest some thing on this , as we are new to this kind of Optimization technique...
This is a very extensive subject - so I will only touch some parts of it - and i write it off the top of my head ...
Even though you point out response speed as your main concern I would suggest that you take a very hard and serious look on how to reduce the size of the data model. This will in many cases contribute to a significant speed increase. Remember that each concurrent user using the application from a server will add up to ~10% extra in in-memory size to the base-line data model size. So with the figures you mention I guess that you could actually be running into problems pretty quickly. The QVW-file size is much much more compressed than what you will see when it is loaded into memory. It might take as much as 4-20 times the size in-memory compared to disk. So with almost 3GB you might have an in-memory application approaching 30-60 GB already ... with only one user hitting the app. Add 10% for every user ... with 10 users it will become 60-120 GB...
The best way of seeing what size the app is taking in memory is to use the Task Manager and start QlikView Desktop and just load the application as a developer. Then you can have a look at QV.EXE and see the number of KB/MB it has allocated - which is a good indication at what you need in a server too for the base-line for the application.
Moving expressions from the UI/Charts to the load script in itself does not increase speed or reduce size - but it will take the application more easily maintainable and easier to keep the quality at a necessary level.
Doing calculations while running the load script and incorporating the results in the data model can speed up things but it might also increase the size of the data. It will normally be of a great benefit to generate flags in the data model to support more speedy set analysis. A numeric flag/indicator will be much easier to incorporate and much speedier to calculate in a set expression - so that way you move much of the burden of a set expression to the load script - getting often a speedier calculation.
A few of the most valuable techniques to reduce the data model size are:
Remove unnecessary fields
Look at fields that have many distinct values compared to the number of total values for the field that is referred to as cardinality. The de-duplication that QlikView does behind the curtains is very much dependent on this ratio being high. Then the de-duplication - which is a form of compression - will give you the most space saving. So break up low-ratio total/unique fields into parts and also round or truncate fields to the lowest precision - like removing the time-part in Date fields by using Floor().
If there are numbers that could be kept as integers instead of floating point - they will consume half the size and compute much faster too.
If you can split your application into two or more versions with one having the most used time horizon and the other less used having the full time horizon and history. Then the UI can be kept the same. It is called application partitioning/sementation and many articles describe this. You can even make the switch between them quite seamless by making the UI conformant and transferring the state when jumping (via a button or otherwise) between applications.
Avoid Dual-fields if possible - they consume much more space - and QlikView even makes some numbers loaded via the load script to dual even when it shouldn't and that will contribute to size increase.
Lastly obtaining memory statistics for your applications is crucial - go to Document Properties, press Memory Statistics button and save the .mem file. It is a CSV-file that you can read from a new memory analysis application you can make yourself or check out Rob Wunderlich's "Document Analyzer". Even the free QlikView Governance Dashboard can help you with the introspection you need to optimize your application.
I have probably missed important bits here - but hopefully others will try to help you out with this question. Research the community, blogs and several of the QlikView books available now in 2015 covers the topic too. Lastly the QlikTech course named "Advanced Topics in Design and Development" also covers this to some extent.
Moving Expressions done with Set Expressions into calculations done during a Load Script run is quite feasible and often times absolutely necessary. It is certainly not a trivial task because you need to employ different techniques and have to have a broader skills sets to solve it. Using LOAD aggregation functions with GROUP BY and IF's in combination creating calculated fields will be the main tools. The more ad-hoc dynamic portion to give your users full flexibility in their analysis while using the application has to be left in the UI still. Creating this balance can be quite an art.
