GROUP BY PERFORMANCE

[Anonymous]

HI

I HAVE A VERY BIG TABLE 500 FIELDS 40M ROWS TRYING TO GROUP THIS TABLE AND IT TAKES FOREVER AROUND 8HRS

ANY IDEAS HOW TO IMPROVE THIS GROUP BY???

TRIED TO CUT THIS TABLE TO 5 SEPARATE TABLES  THAT HAVE 100 FIELDS AND 40M ROWS AND THEN JOIN BACK ALL 5 TABLES BACK TO ONE THIS DIDN'T IMPROVE THE PERFORMANCE BY MUCH

ANY OTHER IDEAS?

THANKS

JT

[israrkhan]

Are you grouping in script, or you mean group in front end(dimensions).

[Gysbert_Wassenaar]

First of all do not load fields or records you are not using in the front end charts and expressions. Secondly, if you load data from a database you could let the database server do the grouping instead of doing it in Qlikview.

[jagan]

Hi,

To improve performace load the required fields, I think you may not required all the columns in the dashboard, so load whatever columns required so that the load time and qlikview file size will be reduced.  Also reduce the unnecessary records, for example if you required last 3 years required then load only that data.

Instead of grouping in SQL try to Group in Qlikview, doing this improve the performance.

Regards,

Jagan.

[Anonymous]

Hi the requirement is for all the fields  so i cant down size the data and the table holds hourly data that need grouping to daily i only hold 14 hourly days.

if i do the group not in the script but with qlikview (in the gui) i end up with a very big model 25GB

basically my problem is that i have an hourly table i need to change to a daily table. The data base cant/wont do it. so i have to build the daily table and this group by takes around 8 HRS. any ideas on how to improve this time ???

[jagan]

Hi,

Instead of using Group by just remove the time part by using Left(), so that we can avoid Group by operation. Group by takes more time and save this table to QVD.  Now try in Qlikview to group the data.

Regards,

Jagan.

[DavidFoster1]

Suggestions:

1. Load the source table into QlikView without any group by and then if you must do a group by do it on the resident temporary table.
2. Look at using an incremental loading approach to get each day's data into separate QVDs, Then you can build dedicated apps to look at different timeframes.
3. Challenge strongly the need for so many fields in a single transaction/fact table. Is there no way to turn it into a star schema (or derivative of)?