When a QVD is loaded unoptimized, numbers may be converted to strings which can increase the amount of RAM required. This can have the odd effect of requiring more RAM to load a subset of rows from a QVD. I first blogged about this in May 2008:
Over a year ago, I managed to get this accepted as a bug by QT and was hoping it would be fixed in V11. It has not, and as I press this bug, I'm asked "what is the business case" for working on this bug? For me, I spend extra hours reworking scripts to maintain optimized loads when I'm concerned about RAM footprint. This is only necessary in large applications, which in my practice I get a number of.
My question is, is this issue a concern for anyone else? I don't think it's fair to say you are a concerned party if you just became aware of this now (unless it explains an existing problem). But have you had to spend time dealing with this in the past?
Hi Rob , it is a concern for me too!!!! it is unacceptable that you can reproduce this issue and QT is asking for a business case....when the same QT in their documents reccomend to have numeric fields instead text fields for better performance.
That's a good point and I'd say that RAM is always an issue when certain number of rows is reached. It matters almost in every case of an enterprise deployment, where thousands of millions of records are loaded and a two or three tier model is used, and the memory footprint for each row is multiplied by millions.
Well, QVD should be loaded optimized as much as possible and QT has been improving in this direction, allowing doing MAPPING LOADS from QVD files, and reducing the number of clauses that make a LOAD from QVD unoptimized, and makes sense as long as QlikView is aiming more and more enterprise deployments so, why not taking into account this issue?
Let me know if I can be of any further help say creating an example application that uses both QVD loads and test performance and RAM usage in both cases, thinking on a big, enterprise development.
Thanks for your comments. It's fairly easy to reproduce and I have provided samples in the past.
I've retested in QV11 with my samples and note that numbers (in my sample qvw) do not get expanded on an un-optimized load, but dates still do. I haven't confirmed if this hold true in more cases. So there appears to be some improvement in QV11.
I've heard of this issue before, but I don't recall feeling the impact personally, maybe because I'm doing everything possible to keep all QVD loads optimized for large databases. Maybe I should pay more attention to the memory footprint...
Knowing the problem, it would be helpful to understand all the implications of it and to know what to avoid - for example, it sounds like we are better off storing dates as numbers and formatting them later.
Miguel - do you have any documentation around the recent enhancements, for example what clauses don't make the load unoptimized anymore. I was always aware of the WHERE EXISTS() rule, and I'd love to learn about any recent developments in this area.
In V10, the document default and internal storage for integers may be converted from number to mixed in an unoptimized reload. You can usually correct this by changing the document type in Number and checking survive reload. In V11, the integers to mixed seems to be fixed.
In V11, Dates, which are stored as integers in a QVD, will get converted to mixed in an unoptimized load.
I have dealt with this on an app by app basis, because I'm typicaly only looking at it when tuning very large apps. I use the mem file and Qlikview Optimizer to identify fields where I may get some useful RAM savings based on number of values and storage format.
.png "Miguel_Angel_Baeyens"){kind=avatar}
