Performance tuning during GROUP BY

[jonnaamb]

Hello experts, I have a huge amount of data in a QVD with 1.8 GB so the server performance got degraded and this piece of code runs for 40 minutes alone. In order to do performance tuning, what should be done? Please suggest. Many thanks in advance.

[Fact_Consolidation]:
NOCONCATENATE
LOAD
[Material_View_Key],
[Order Number],
Ord_Type_Key,
Ord_Type,
Source,
[Customer_View_Key],
[Exclude Wrigley],
[PO Number], 
[Calendar Date],
[Material Key],
[Customer Number],
[Ship To #],
[SFH Geo ShipTo #],
[SFH Geo ShipTo Person #],
[Direct Customer Number],
[Distribution Channel],
[Division],
[Sales Organization],
[FlatFile],
[Event Template], 
SUM([Invoiced_GSV]) AS [Invoiced_GSV],
SUM([Open_GSV]) AS [Open_GSV],
SUM([Plan_GSV]) AS [Plan_GSV],
SUM([Invoiced_QTY]) AS [Invoiced_QTY],
SUM([Open_QTY]) AS [Open_QTY],
SUM([Delta_Invoiced_GSV]) AS [Delta_Invoiced_GSV],
SUM([Delta_Open_GSV]) AS [Delta_Open_GSV],
SUM([Delta_Invoiced_QTY]) AS [Delta_Invoiced_QTY], 
SUM([Delta_Open_QTY]) AS [Delta_Open_QTY]  
,SUM(DemandREV_QTY) AS DemandREV_QTY
,SUM(DemandREV_GSV) AS DemandREV_GSV 
,SUM([CLTREV_GSV]) as [CLTREV_GSV] 
  ,SUM([Original Quota GSV]) AS [Original Quota GSV]
,SUM([Original Quota Qty]) AS [Original Quota Qty]
,SUM([Latest Quota GSV]) AS [Latest Quota GSV]
,SUM([Latest Quota Qty]) AS [Latest Quota Qty]
  ,SUM(Seasonal_Plan_GSV) AS Seasonal_Plan_GSV
  ,SUM([NS Original Quota GSV]) AS [NS Original Quota GSV]
,SUM([NS Original Quota Qty]) AS [NS Original Quota Qty]
,SUM([NS Latest Quota GSV]) AS [NS Latest Quota GSV]
,SUM([NS Latest Quota Qty]) AS [NS Latest Quota Qty]
,SUM(NonSeasonal_Plan_GSV) AS NonSeasonal_Plan_GSV
  ,Sum([Customer Forecast QTY]) as [Customer Forecast QTY]
,Sum([Customer Forecast GSV]) as [Customer Forecast GSV]
FROM [$(TRLPath)\tmp_Fact_Integration_final.qvd] (qvd)

GROUP BY
[Material_View_Key],
[Order Number],
Ord_Type_Key,
Ord_Type,
Source,
[Customer_View_Key],
[Exclude Wrigley],
[PO Number],
[Calendar Date],
[Material Key],
[Customer Number],
[Ship To #],
[SFH Geo ShipTo #],
[SFH Geo ShipTo Person #],
[Direct Customer Number],
[Distribution Channel],
[Division],
[Sales Organization],
[FlatFile]
,[Event Template]
;

Thanks and regards,

Ambareesh Jonnavittula

[sunny_talwar]

I would be curious to know if there is a way to improve this, but AFAIK there might not be any...

[Clever_Anjos]

Unfortunately there´s no easy way of speed this up.

Group by operations in qlik seems to be 'mono core', no matter how many CPU cores  you have, QV will use only one

One option is splitting your qvd based on [Calendar Date] and grouping only the last "n" months, keeping in disk the old ones already aggregated

[Clever_Anjos]

One possible way of slightly improve is identify the real granularity of the fields and group by only for them and use MinString in the correlated fields

Example:

LOAD
[Material_View_Key],
[Order Number], // mininum 'grain'

[Distribution Channel],
[Division],

// other fields and functions

Group by

[Material_View_Key],
[Order Number],

[Distribution Channel],
[Division];

Becomes:

LOAD
[Material_View_Key],
[Order Number], // mininum 'grain'

minstring([Distribution Channel]) as [Distribution Channel],  // supposing they are always equal for each [Order Number]
minstring([Division]) as [Division],

// other fields and functions

Group by

[Material_View_Key],
[Order Number];

[Kushal_Chawda]

I would suggest to perform this aggregation part in underlying database which will be faster in native database. You can create the table in database with this aggregation part. After that you can pull that table in QlikView and store it in QVD