4 Replies Latest reply: Oct 10, 2017 2:13 PM by Liam Hanninen RSS

    Optimize Group By

    Rupinder Singh

      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.