Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Recipe for a Memory Statistics analysis

In a previous blog post I described the internal data tables and the symbol tables. (See “Symbol Tables and Bit-Stuffed Pointers”). These tables constitute the QlikView internal data model. Then there are the state space vectors that keep track of the user’s selections (See “Colors, states and state vectors”).

In addition to these, there are other structures used to calculate the sheet objects. Sometimes a sheet object can use quite a lot of memory, e.g., a chart with many dimensions.

Often you need to ask yourself – “What in this application uses a lot of memory? What can I improve or optimize?” Is it the data model itself or is it the symbol tables? Or is there a chart that uses a lot of memory?”

To get an answer to these questions, you can use the memory statistics tool. Here follows a basic recipe for a memory analysis:

  • Create a memory statistics file from the application you want to analyze (Document Properties -> General -> Memory Statistics). This will export some memory statistics data to a tab separated file.
  • Create a new QlikView document in which you load the created file.
  • Create list boxes for the fields Class, Type, and Subtype.

To understand what these fields display, see the table below.

Id types.png

The Class field tells you whether the memory used is part of the internal database (data tables and symbol tables), the state space (the selections), the sheet objects (volatile structures to calculate the sheet objects), or a variable. The Type and SubType fields give you additional information about where memory is used.

  • Create a drill-down group of the fields Class, Type, and Subtype named ">Type".
  • Create a stacked bar chart with Id as first dimension, >Type as second dimension, and Sum(Bytes) as expression.
  • Sort the chart descending according to y-value.
  • Restrict the chart to show only the first 10 bars.

You should now have a graph similar to the one below.

Memstat Chart.png

In this you can see which objects, tables, or fields are consuming memory in your application. The bars to the left are the ones that use a lot.

Now you can start to optimize!

If most of your memory consumption is made by sheet objects, look at them and see if there is anything you can do. Does this chart have too many dimensions? Is the expression too complex? Do I really need to sort this list box with a very large number of distinct values? Do I need to show it at all?

If most of your memory consumption is due to database symbols, look at whether you can use the autonumber function to make these symbol tables use less memory.

If most of your memory consumption is made by database tables, you might want to remove columns, or aggregate data. Or maybe realize that optimization isn’t worth wile…

One small word of warning: The numbers in the memory statistics analysis should not be trusted too literally – they do not always describe the situation completely. For instance, if a calculation is cached, it will not show up in the memory usage. Further, if an object has not been opened yet, its memory consumption is unknown and will display as zero. But in spite of these shortcomings, the memory statistics function will most of the time help you find where memory is used.

HIC

24 Comments
hariharasudan_p
Contributor

Nice one HIC. Great..

0 Likes
1,752 Views
Partner
Partner

We've been using this as part of our "formal development" process for a while and developers can gain great insight to how to improve the quality of a QVW by analysing as you have stated.

I am noticing on QlikView 11 that RAM use on Task Manager is very different from the MEMory stats data.  Could you reply with some reasons for this; am guessing it is part of QV.exe developer but also caching etc.

Also, does set analysis and alternate states increase state space?

I believe this is another hidden QlikView jewel.  Few other products give this sort of insight to the internal workings and because QlikView does, we create even more elegant solutions - so my message to QlikTech is please keep this functionality healthy.

1,752 Views
hariharasudan_p
Contributor

Yup. Good question adam. HIC we are waiting for your reply.

1,752 Views

The memory statistics file only shows memory used by the document, whereas the Task Manager also shows RAM used by the program itself and by the cache (the cache does not belong to a specific document - it is common for all documents).

Alternate states will definitely increase the state space. Not sure if this is correctly reflected in the displayed number, though.

HIC

1,752 Views
Partner
Partner

I looked at the question and I expect the problem you have is that you have not navigated through all the sheets.

In my experience, you should open the QVW, make a selection, move to the next sheet, push clear, move to the next sheet and make a selection - do this for every sheet.

While you do the above, QlikView will write the RAM usage into the MEMory stats area.  The calculation time of the sheet objects are:

1. Amount of time to evaluate on last click

2. Average time to evaluate

I think the RAM amount is based on the last click.

If you do not activate a sheet object (when a Chart is minimised), QlikView will not evaluate the object and so you will have no MEMory stats... you have to be sure to Navigate through the document in a way that activates all the objects.

1,752 Views

Adam is completely right.

Also, take a look at the "QlikView Optimizer" application delivered with QV 8.5. http://t.co/uEvnaRRi

HIC

0 Likes
1,752 Views
Partner
Partner

I have a couple of questions the "QlikView Optimizer" application and memory metadata:

1) What do negative calculation times represent? I ran the optimizer against a poor-performing application and some of the objects generated negative calculation times. Some of them also generated extremely high calculation times, which leads to the next question.

2) What is the scope of the memory statistcs that are collected? That is, do the represent activity since the last time the file was saved, or since it was last opened, or since it was created? In the example above we saw calculation times that were longer than we had the file open before exporting the memory file, and it wasn't clear what this represented.

I'll post some screenshots when I get access to the file again.

Thanks,

James

0 Likes
1,752 Views
Partner
Partner

Here is a screenshot showing negative calculation time. It's associated with a variable containing a complex expression, not an actual object.

neg_calc_time.png

0 Likes
1,752 Views
Partner
Partner

One more self-reply. I think I found a clue in the QlikView Help for sheet properties, Objects tab. It describes the Memory column this way:

"The time in milliseconds needed for the last recalculation of the object's contents."

That sounds like the scope of memory statistics is only the last recalculation. Is that right?

0 Likes
1,752 Views

It is indeed the last recalculation. But, I would be careful using the calculation times. Or - I wouldn't trust them to 100%. The calculation algorithm was developed in earlier versions, before we improved the caching. When the cache is used, the result of the calculation is fetched much faster than if it were to be calculated. So, I think the calculation times are much too small in the cases when the cache is used.

HIC

0 Likes
1,752 Views
Partner
Partner

I concur, Henric. When I tested this using the Sheet Properties window, a selection took 116,812 milliseconds immediately after opening the .qvw. After making other selections, clearing, and making that initial selection again, the same selection took only 4,797 milliseconds.

Do you have any idea why I would see a negative number in recalculation time?

0 Likes
1,752 Views

No. I'd say it's an error.

HIC

0 Likes
1,752 Views
Partner
Partner

Hi HIC and other techies

Any news on mem states being updated to cope with new features in QV10/11?

On my analysis I am seeing strong indication that cpu/ram are missed:

- for the aggr() work area

- Listboxes - especially with sort expressions and display expressions

- as before, the set analysis/alternate states

- container objects

Tx, Adam

0 Likes
1,752 Views
saurabh_pandit
Contributor

Hi All,

I too would like to know if Containers are considered in Mem Stats for QV11.

Anyone has any updates on it?

Thanks.

0 Likes
1,752 Views
techvarun
Valued Contributor II

Thank you HIC its very useful

0 Likes
1,752 Views
Not applicable

I was using the Optimizer with this example data:

1CHARSTRING 1DIGITNUM 2CHARSTRING 5CHARSTRING
A 1 AB ABCDE

And getting these results

ClassTypeSubTypeIdBytesCountSize
DatabaseFieldSymbols1CHARSTRING717.00
DatabaseFieldSymbols2CHARSTRING818.00
DatabaseFieldSymbols1DIGITNUM11111.00
DatabaseFieldSymbols5CHARSTRING11111.00

Why does a 1 digit number use the same as a 5 char string?

0 Likes
1,752 Views

It's complicated and I don't know all the details...

But - first of all, you're looking at the symbol tables, i.e. the tables that store the unique values. Characters and numbers are treated completely differently here.

Numbers are stored as duals with 8 bytes for each numeric value + 1 byte for each digit for the formatting. So, your '1DIGITNUM' would then need 9 bytes for the value and its formatting. The extra two bytes are som kind of overhead that I do not know the details of.

Strings are stored with sometimes 1 byte per character, sometimes several. (Try Unicode characters, and you'll see.) Your 3 char fields all use 6+N bytes, i.e. the 6 bytes are overhead, probably for sorting, but also here I am not sure for what these are used.

If you load several records, you will see some other interesting effects, e.g. that consecutive numbers use 0 bytes - they are not stored since they are implicit, so QlikView figures out which number it is anyway.

HIC

0 Likes
1,752 Views
Not applicable

Hi Henric

Please post PE enabled file.

BR

0 Likes
1,752 Views
sujeetsingh
Honored Contributor III

Thanks A lot ..Henric

0 Likes
1,752 Views
evan_kurowski
Valued Contributor

What if it were using fixed points to calculate generation times, and the system clock/calendar was changed?

Like it said hey my calculation finished at 12:00 on 1/1/2014 and then you set the system time to 12/15/2013, interact with the app and it says "my last calculation took -15 days"?

Just throwing out a guess.

1,752 Views
agni_gold
Valued Contributor III

Hi Henric,

Is there any way we can automate memory file generation via any macro or anything?

I have tried writing this macro, but its giving error

sub mem_read

Set docObj = Qv.OpenDocEx (D:\Neha\123.qvw,0,true, "", "", "", false)

docObj.SaveMemoryStatistics(D:\Neha\ & "MemoryStatistics.mem")

end sub

Thanks,

Agnivesh

0 Likes
1,752 Views
agni_gold
Valued Contributor III

Is there any way I can write a macro to do this:

Document Properties -> General -> Memory Statistics


So that i dont have to everytime go and manually do this step to generate memory file


Regards,

Agnivesh

0 Likes
1,752 Views

Yes, you can use a macro for this. In the following macro I have the file name for the mem stat file in a variable:

Sub StoreMemStat

set vFileName = ActiveDocument.Variables("vMemStatFileName")

ActiveDocument.SaveMemoryStatistics vFileName.GetContent.String

End Sub

HIC

0 Likes
1,752 Views
sanjyotpatkar
Contributor III

Hi Henric

I am facing an issue with while loading a certain graph that calculates the outstanding count and the aging of a ticket. I have used the concept of Generating missing dates for calculating the missing dates. As a result the script becomes heavy

This is the script that i have used to calculate the aging and outstanding .  So I referred to your blog to optimize the speed. I ran the memory file through the Qlikview optimizer and found that the most consuming memory is the creation of reference open dates. I have pasted the memory distribution pie charts below the script.

Although i have tried to use preceding load, set anaylsis in the expression and flags the graphs are taking a very long time to load. Is there a way to improve the performance of the graphs and is there a way for me to optimize the below script? Can you please suggest what I may be missing out so that the speed of the graphs is improved?

load
[TR #] ,

if(year(ReferencedateOpened)>'2013',Weekend(ReferencedateOpened)) as ReferencedateOpenedweek,

if(year(ReferencedateOpened)>'2013',monthname(ReferencedateOpened)) as ReferencedateOpenedMonth,

if(monthname(ReferencedateOpened)<=AddMonths(today(),-1) and monthname(ReferencedateOpened)>=AddMonths(Today(),-14),monthname(ReferencedateOpened)) as Last13MonthsReferencedateOpened,

if(WeekStart(ReferencedateOpened)<=WeekStart(today()) and WeekStart(ReferencedateOpened)>=WeekStart(Today(),-8),WeekStart(ReferencedateOpened)) as Last8WeeksReferencedateOpened,

if( IsNull([Reference Resolve Date%])=-1 and WeekStart([Reference Open Date%])<=WeekStart(ReferencedateOpened),1,0 ) as vflag_null_Resolve_dates,//Calculating weekly data

if( WeekStart([Reference Resolve Date%]) > WeekStart(ReferencedateOpened) and WeekStart([Reference Open Date%])<=WeekStart(ReferencedateOpened),1,0) as vflag_non_null_Resolve_dates, //Calculating weekly data

if(IsNull([Reference Resolve Date%])=-1 and Monthname([Reference Open Date%])<=Monthname(ReferencedateOpened),1,0) as vflag_null_Resolve_dates_monthly,

if(monthstart([Reference Resolve Date%]) > monthstart(ReferencedateOpened) and monthstart([Reference Open Date%])<=monthstart(ReferencedateOpened),1,0) as vflag_non_null_Resolve_dates_monthly,

if( IsNull([Reference Resolve Date%])=-1 and Monthname([Reference Open Date%])=Monthname(ReferencedateOpened),1,0) as vflag_null_Resolve_dates_StillOpen,

//For 13th month we need to consider tickets that have been resolved in next month as outstanding as well.hence monthstart(Resolved_PR%) > AddMonths(today(),-1) has been considered

if( Monthname([Reference Open Date%])=Monthname(ReferencedateOpened) and monthstart([Reference Resolve Date%]) > AddMonths(today(),-1),1,0) as vflag_non_null_Resolve_dates_StillOpen,

//Calculating monthly aging buckets for Incident Tickets

ifIsNull([Reference Resolve Date%])=-1 and Monthname([Reference Open Date%])<=Monthname(ReferencedateOpened),

if(networkdays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY'))<=5,'1 week',
if(networkdays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY'))<=15,'2-3 weeks',
if(networkdays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY'))<=25,'1 month',
if(networkdays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY'))<=45,'2 months','>2 months'

)))))
as vflag_Aging_null_Resolve_dates_Incident,


if(monthstart([Reference Resolve Date%]) > monthstart(ReferencedateOpened) and monthstart([Reference Open Date%])<=monthstart(ReferencedateOpened),
if(networkdays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY')) <=5,'1 week',
if(networkdays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY')) <=15,'2-3 weeks',
if(networkdays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY')) <=25,'1 month',
if(networkdays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY')) <=45,'2 months','>2 months'
)))))
as vflag_Aging_non_null_Resolve_dates_Incident,

if(WeekStart(ReferencedateOpened)<=WeekStart(today()) and WeekStart(ReferencedateOpened)>=WeekStart(Today(),-8),
ifIsNull([Reference Resolve Date%])=-1 and WeekStart([Reference Open Date%])<=WeekStart(ReferencedateOpened),

if(networkdays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY'))<=5,'1 week',
if(networkdays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY'))<=15,'2-3 weeks',
if(networkdays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY'))<=25,'1 month',
if(networkdays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY'))<=45,'2 months','>2 months'

))))))
as vflag_Aging_null_Resolve_dates_Incident_weekly,

if(WeekStart(ReferencedateOpened)<=WeekStart(today()) and WeekStart(ReferencedateOpened)>=WeekStart(Today(),-8),
if(WeekStart([Reference Resolve Date%]) > WeekStart(ReferencedateOpened) and WeekStart([Reference Open Date%])<=WeekStart(ReferencedateOpened),
if(networkdays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY')) <=5,'1 week',
if(networkdays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY')) <=15,'2-3 weeks',
if(networkdays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY')) <=25,'1 month',
if(networkdays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY')) <=45,'2 months','>2 months'
))))))
as vflag_Aging_non_null_Resolve_dates_Incident_weekly,


if( IsNull([Reference Resolve Date%])=-1 and Monthname([Reference Open Date%])<=Monthname(ReferencedateOpened) , NetWorkDays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY')) )as vflag_Elapsed_Time_null_Resolve_dates_Incidents,

if(monthstart([Reference Resolve Date%]) > monthstart(ReferencedateOpened) and monthstart([Reference Open Date%])<=monthstart(ReferencedateOpened), NetWorkDays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY'))) as vflag_Elapsed_Time_non_null_Resolve_dates_Incidents,

if(WeekStart(ReferencedateOpened)<=WeekStart(today()) and WeekStart(ReferencedateOpened)>=WeekStart(Today(),-8),
if( IsNull([Reference Resolve Date%])=-1 and WeekStart([Reference Open Date%])<=WeekStart(ReferencedateOpened) , NetWorkDays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY')))) as vflag_Elapsed_Time_null_Resolve_dates_Incidents_weekly,

if(WeekStart(ReferencedateOpened)<=WeekStart(today()) and WeekStart(ReferencedateOpened)>=WeekStart(Today(),-8),
if(WeekStart([Reference Resolve Date%]) > WeekStart(ReferencedateOpened) and WeekStart([Reference Open Date%])<=WeekStart(ReferencedateOpened), NetWorkDays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY')))) as vflag_Elapsed_Time_non_null_Resolve_dates_Incidents_weekly,

//Calculating aging buckets for Problem Tickets

if(IsNull([Reference Resolve Date%])=-1 and Monthname([Reference Open Date%])<=Monthname(ReferencedateOpened),
if((today()-[Reference Open Date%])<=90,'0-3 months',
if((today()-[Reference Open Date%])>90  and (today()-[Reference Open Date%])<=180,'3-6 months',
if((today()-[Reference Open Date%])>180 and (today()-[Reference Open Date%])<=270,'6-9 months',
if((today()-[Reference Open Date%])>270 and (today()-[Reference Open Date%])<=360,'9-12 months',
if((today()-[Reference Open Date%])>360,'>12 months'
))))))
as vflag_Aging_null_Resolve_dates_Problems,


if(monthstart([Reference Resolve Date%]) > monthstart(ReferencedateOpened) and monthstart([Reference Open Date%])<=monthstart(ReferencedateOpened),
if((monthend(ReferencedateOpened) - [Reference Open Date%]) <=90,'0-3 months',
if((monthend(ReferencedateOpened) - [Reference Open Date%])>90  and (monthend(ReferencedateOpened) - [Reference Open Date%])<=180,'3-6 months',
if((monthend(ReferencedateOpened) - [Reference Open Date%])>180 and (monthend(ReferencedateOpened) - [Reference Open Date%])<=270,'6-9 months',
if((monthend(ReferencedateOpened) - [Reference Open Date%])>270 and (monthend(ReferencedateOpened) - [Reference Open Date%])<=360,'9-12 months',
if((monthend(ReferencedateOpened) - [Reference Open Date%])>360,'>12 months'
))))))
as vflag_Aging_non_null_Resolve_dates_Problems;

Open_Referencedates:
load
[TR #] ,
date([Reference Open Date%] + IterNo() - 1) as ReferencedateOpened,
[Reference Open Date%],
[Reference Resolve Date%]
Resident Resident_Ticket_Dump

while IterNo() < today() - [Reference Open Date%] +1;

My expression for aging

(sum(aggr(sum( DISTINCT{<[Referred to CPS]={'Y'},RTC_Status={'ReOpened','To be Assigned','Working'}>}vflag_Elapsed_Time_null_Resolve_dates_Incidents_weekly),Id,Last8WeeksReferencedateOpened))+
sum(Aggr(sum ( DISTINCT {<
[Referred to CPS]={'Y'},Status={'Open'}>}vflag_Elapsed_Time_null_Resolve_dates_Incidents_weekly),TR,Last8WeeksReferencedateOpened)) +
sum(Aggr(sum ( DISTINCT {<
[Referred to CPS]={'Y'},Application={'RRT','CDR','CRS','OKYC'}>}vflag_Elapsed_Time_null_Resolve_dates_Incidents_weekly),[TR #],Last8WeeksReferencedateOpened)) +
sum(Aggr(sum ( DISTINCT {<
[Referred to CPS]={'Y'}>}vflag_Elapsed_Time_non_null_Resolve_dates_Incidents_weekly),Id,Last8WeeksReferencedateOpened)) +
sum(Aggr(sum ( DISTINCT {<
[Referred to CPS]={'Y'}>}vflag_Elapsed_Time_non_null_Resolve_dates_Incidents_weekly),TR,Last8WeeksReferencedateOpened)) +
sum(Aggr(sum ( DISTINCT {<
[Referred to CPS]={'Y'},Application={'RRT','CDR','CRS','OKYC'}>}vflag_Elapsed_Time_non_null_Resolve_dates_Incidents_weekly),[Incident Number],Last8WeeksReferencedateOpened))  )

/

(count(DISTINCT{<vflag_non_null_Resolve_dates={'1'},
[Referred to CPS]={'Y'}>}[Incident Number])+
count(DISTINCT{<vflag_non_null_Resolve_dates={'1'},
[Referred to CPS]={'Y'}>}Id)+
count(DISTINCT{<vflag_non_null_Resolve_dates={'1'},
[Referred to CPS]={'Y'}>}TR)+
count(DISTINCT{<vflag_null_Resolve_dates={'1'},Status={'Open'},
[Referred to CPS]={'Y'}>}TR) +
count(DISTINCT{<vflag_null_Resolve_dates={'1'},RTC_Status={'ReOpened','To be Assigned','Working'},
[Referred to CPS]={'Y'}>}Id)+
count(DISTINCT{<vflag_null_Resolve_dates={'1'},
[Referred to CPS]={'Y'}>}[Incident Number]) )

memory usage.JPG

0 Likes
1,752 Views