Poor performance in QV document

[#1]

Hi,

I have developed a Qlikview application which takes too much time to perform the calculations. The details are given below:

The document has 220 million records and the size is 650 MB. The fact data is obtained from 7 tables and all these data are concatenated into a single fact table in QV model. Each source fact table data is identified by a flag value (1/0). The charts take takes too much time (more than a minute) to show the results. Our server is a virtual machine, Intel XEON processor 2.3GHZ with 32 GB RAM.

The following are the expressions of the calculations which takes more time:

num((sum({$<[Calendar Day] = {">=$(=Date($(v_Start Date)))<=$(=Date($(v_End Date)))"},[Sale Flag]={1},[Sales Measure]=,[Financial Period]=,[Financial Quarter]=,
[Financial Year]=,Month=,Quarter=,Week=,Year=,[LC Day]=,[Marketing Question]=,[AAMS SKU]=,[Other Product Name]=,[SKU Availability Reason Name]=,[Contract Start Date]=,[Contract End Date]=,[Contract Type]=,[Contract Status]=,[Contract Group Name]=>}[Sales Value])/
AGGR(SUM({<[Sale Flag]={1},[Other Product Name]=,[SKU Availability Reason Name]=,[LC Day]=,[AAMS SKU]=,
[Marketing Question]=,[Local SKU]=,[Product Group]=,[Logista Product Group]=,
[Logista Sub Product Group]=,[Contract Start Date]=,[Contract End Date]=,[Contract Type]=,[Contract Status]=,[Contract Group Name]=>}[Sales Value]),Outlet,$(v_Time Group Member))),'##.##%')


num(AVG({$<[Calendar Day] = {">=$(=Date($(v_Start Date)))<=$(=Date($(v_End Date)))"},[Sale Flag]={1},[Sales Measure]=,[Financial Period]=,[Financial Quarter]=,
[Financial Year]=,Month=,Quarter=,Week=,Year=,[LC Day]=,[Marketing Question]=,[AAMS SKU]=,[Other Product Name]=,[SKU Availability Reason Name]=,[Contract Start Date]=,[Contract End Date]=,[Contract Type]=,[Contract Status]=,[Contract Group Name]=>}[Sales Value]),'##.0##')

sum({$<[Calendar Day] = {">=$(=Date($(v_Start Date)))<=$(=Date($(v_End Date)))"},[Sale Flag]={1},[Sales Measure]=,[Financial Period]=,[Financial Quarter]=,
[Financial Year]=,Month=,Quarter=,Week=,Year=,[LC Day]=,[Marketing Question]=,[AAMS SKU]=,[Other Product Name]=,[SKU Availability Reason Name]=,[Contract Start Date]=,[Contract End Date]=,[Contract Type]=,[Contract Status]=,[Contract Group Name]=>}[Sales Value])

I am sure the document can be optimized to improve the performance. So far, I am not able to identify the root cause in order to fix this. Please provide your suggestions to improve the performance.

Thanks,

Haneesh

[#2]

If you need those crazy set analysis expressions, move away from virtual to real hardware

-Alex

[#3]

Thanks Alex.

I understand that it is more efficient to have the real hardware instead of virtual machines. Can't we improve the performance on virtual machines? Is there some server side settings changes which could help us improve the performance?

[#4]

Try to define first variables for the set expressions. That makes it easier to read, and to find if those are correct in the first place.

Probably you could improve the virtual machine (balooning memory for VMWare?). But with those set expressions over 200 million rows, might not be enough.

-Alex

[#5]

I am already using variables to define the set expression though I have included the full expression definition in my post. Including the expression definitions with the set variables below:

 <table><col></col> <tbody><tr><td>
num((sum({$(v_Sales Measures)}[Sales Value])/
 AGGR(SUM({$(v_Sales Measures Market Share denom)}[Sales Value]),Outlet,$(v_Time Group Member))),'##.##%')

 </td></tr><tr><td>
num(AVG({$(v_Sales Measures)}[Sales Value]),'##.0##')

 </td></tr><tr><td>
sum({$(v_Sales Measures)}[Sales Value])