One of our Qlik Community Buddy (Rupinder Singh) shared an awesome way to optimize the performance of group by statement but I don't think this got enough lime light which it should have. In order to give it better exposure, I am creating this document from this thread 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.
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.
Order by ID;
NoConcatenate Load ID,Sum(Sales)
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.