topic Re: Optimize Group By in QlikView

Optimize Group By

rupindo_barcap — Fri, 29 Apr 2016 05:22:27 GMT

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.

Re: Optimize Group By

Thu, 21 Jul 2016 14:41:49 GMT

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?

Re: Optimize Group By

DavidFoster1 — Mon, 19 Sep 2016 11:51:48 GMT

Rupinder

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

Re: Optimize Group By

rupindo_barcap — Tue, 06 Dec 2016 14:10:55 GMT

Both Sort and Group By

Re: Optimize Group By

liam_hanninen — Tue, 10 Oct 2017 18:13:20 GMT

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

Re: Optimize Group By

felipedl — Thu, 28 Dec 2017 18:07:23 GMT

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

sunny_talwar — Fri, 29 Dec 2017 14:35:40 GMT

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

Best,

Sunny

Re: Optimize Group By

DavidFoster1 — Mon, 05 Feb 2018 16:53:56 GMT

What does grouping by the table name do?

Re: Optimize Group By

jonopitchford — Tue, 24 Apr 2018 13:53:14 GMT

Amazing

Re: Optimize Group By

Anonymous — Fri, 14 Sep 2018 14:25:36 GMT

Thanks Rupinder

Re: Optimize Group By

HirisH_V7 — Thu, 28 Feb 2019 08:05:47 GMT

Excellent Will give a try over this.

Re: Optimize Group By

HirisH_V7 — Thu, 28 Feb 2019 15:00:08 GMT

Folks i am trying to achieve this with my group by, but i am getting this error. I don't understand, if its working there. 😞

@sunny_talwar @rupindo_barcap @jonopitchford Pl help me.

Re: Optimize Group By

jonopitchford — Thu, 28 Feb 2019 15:17:30 GMT

Try group by ID
it looks like they have accidentally grouped by the table name and not the field name

Re: Optimize Group By

sunny_talwar — Thu, 28 Feb 2019 15:20:03 GMT

I think you are grouping by Table_Sort whereas you might want to Group By ID 🙂

Re: Optimize Group By

HirisH_V7 — Thu, 28 Feb 2019 17:12:12 GMT

Thanks!

But Post above directed me such way, bit confused even when i am trying. Anyway i will check performance this way.

Re: Optimize Group By

HirisH_V7 — Thu, 28 Feb 2019 18:29:10 GMT

Many Thanks! It's actually working.

My reload time was decreased up-to some extent, anyway i will test it on huge data sets as well. As per my understanding in this procedure the order by is doing the required alignment of data for group by, which actually helps Group by to work on spontaneously with every unique data field/no. Beside checking for field/no to do grouping on-every individual occurrence.

Thanks,
HirisH

Re: Optimize Group By

pratyushraizada — Thu, 04 Jun 2020 16:54:19 GMT

I'm using Qlik sense 13.42.1.

I think Qlik has now optimized this. In case we prefix the 'group by' with 'order by' operation, the data model takes a few seconds more to load.

Re: Optimize Group By

Saravanan_Desingh — Thu, 04 Jun 2020 17:05:18 GMT

Thanks for sharing this.

Re: Optimize Group By

basav — Wed, 02 Dec 2020 04:51:52 GMT

Hi All,

I tested this technique on a 173 million records data, Without Orderby Sort, it took 10 min for one aggregation in groupby.

With using order by before group by it actually took 4min to load the 173 million data.

However, There is no changes on overall app run time, it only optimized group by performance. Because whatever the time we save for groupby, orderby will eat that time. So overall app run time has no improvement.

Still, very good and useful technique. Thank you.