topic Re: Performance tuning during GROUP BY in QlikView

Performance tuning during GROUP BY

jonnaamb — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Performance tuning during GROUP BY

sunny_talwar — Thu, 14 Sep 2017 11:42:25 GMT

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

Re: Performance tuning during GROUP BY

Clever_Anjos — Thu, 14 Sep 2017 11:43:48 GMT

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

Re: Performance tuning during GROUP BY

Clever_Anjos — Thu, 14 Sep 2017 12:07:24 GMT

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];

Re: Performance tuning during GROUP BY

Kushal_Chawda — Thu, 14 Sep 2017 12:11:37 GMT

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