Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

rupindo_barcap
New Contributor II

Optimize Group By

For below I am using QV version - 11.2 SR4

I have always had trouble with aggregating my data as "Group by" statements in scripts have always taken too long for my comfort.

For example, to group by  23 million records (2 columns - ID and Sales) it takes me 10 mins.

Table_Group:

Load ID,Sum(Sales)

Resident Table

Group By ID;

Instead of this, If I Order by my data by ID and then run the same Group by clause, I get results backs in less that 2 mins.

Table_Sort:

Load ID,Sales

Resident Table

Order by ID;

Table _Group:

NoConcatenate Load ID,Sum(Sales)

Resident Table_Sort

Group By Table_Sort;

I would have believed QV to internally perform this step, but explicitly implementing this has improved my query times by 500%.

I have tables with 40 columns (36 of which to be grouped by and 4 to be summed) and the query time has gone from 18 to 6 mins.

I have just ensured that my Order By column list is the same as Group By Column List.

So, If Order by is "Order By Column1,Column2,Column3" then Group by is "Group By Column1,Column2,Column3"

I would love to hear back if others in the community also have faced similar challenges and whether this or any other method has helped them improve Group by Performance.

Tags (2)
9 Replies
Not applicable

Re: Optimize Group By

We are running into the same problem with our group bys. We are attempting to get the max date at each account we have for a massive fact table. We added a load resident and an order by before the group by, but it is hard to tell if the order by is improving our run time.hic‌‌ Any insight or better way to load and group data?

dfoster9
Valued Contributor

Re: Optimize Group By

Rupinder

Do your timings include the time to perform the sort, or just the group by?

rupindo_barcap
New Contributor II

Re: Optimize Group By

Both Sort and Group By

liam_hanninen
Contributor

Re: Optimize Group By

This improved my reload time by about 55% in one instance with 25 columns and one group by/order by field. Thanks!

felipedl
Valued Contributor III

Re: Optimize Group By

Awsome strategy Rupinder, just had the same problem and this solution gave a huge performance improvement.

Glad some tried it and helpfully posted it here.

Many thanks!!!!

Felipe.

Re: Optimize Group By

This did work... I didn't think it would, but it did... thanks for showing this.

Best,

Sunny

dfoster9
Valued Contributor

Re: Optimize Group By

What does grouping by the table name do?

jonopitchford
New Contributor III

Re: Optimize Group By

Amazing

rpsrathete
New Contributor

Re: Optimize Group By

Thanks Rupinder

Community Browser