Improve Loading and scrolling time for nestedGalleries

[totti50189]

Hello together,

I would need some wisdom with a performance issue for a data table (Gallery within Gallery).

I have created an App for 400 employees that should show the workload for the next 12 weeks.

We have 400 employees that are assigned two ~20 departments (large departments have up to 80 employees). The employees may work on several projects.

Additionally the tool needs to consider planned holidays and sick leave from two other tables and should display them, too.

We have three filter (Name, Department, Project) and a load button.

After clicking on the load button:

the tool creates a collection for all relevant employees. Afterwards it checks for each employee on what projects he works in the next 12 weeks and loads the data for each employee/project (row) and week (column). If the employee has holidays it add a "project" as well.

So in the end for 50 employee we may have 100 rows (sometimes several projects for an employee and/or holidays), some reference columns (name and projects name, project start and project end) and 12 data columns (one value for each week).

For each data field, there are two values shown (the default ERP value) and one that can be modified (therefore a datatable wouldn't work)

The problem is that creating this collection is unbelievable slow (>1 Minute) and scrolling in the App is also quite "lagging". 


Has somebody any ideas how I could improve the performance/User Experience. 

Attached a screenshot of the data table: 

And that is the screen how we store the data 
Ressource ID is the Employee ID, CostCenter is the Department

And that is the current code:

//filter the colResource collection to help with data amount
If(CountRows(cbCostCenter.SelectedItems) = 0 || IsBlank(cbCostCenter.SelectedItems), 
    //clear collections if there is no selection
    Clear(colResource);
    Clear(colActualHours),
    UpdateContext({showLoading: true});

    //load data in collections if there is a selection
    Clear(colResource);
    Clear(colActualHours);
    //loop through the selections in this combobox
    ForAll(cbCostCenter.SelectedItems,

        //get central planner data into a collection add source column
        Collect(colResource, AddColumns(ShowColumns(Filter(List_ERP_CentralPlanner, UC_CostCenter = CostCenter),"UC_CostCenter","UC_Desc", "UC_Email", "UC_EndDate","UC_EndYearWeek", "UC_Name", "UC_Percentage", "UC_Project", "UC_Resource", "UC_StartDate", "UC_StartYearWeek", "UC_WorkOrder"),"Source", "ERP", "Edit", false));
        //add sharepoint projects into same collection
        Collect(colResource, AddColumns(RenameColumns(Filter(List_SharePoint_Plan, SP_CostCenter = CostCenter),"SP_Resource", "UC_Resource", "SP_Desc", "UC_Desc", "SP_StartDate","UC_StartDate", "SP_StartYearWeek", "UC_StartYearWeek", "SP_EndDate", "UC_EndDate", "SP_EndYearWeek", "UC_EndYearWeek", "SP_Percentage", "UC_Percentage", "SP_Email", "UC_Email", "SP_Name", "UC_Name", "SP_CostCenter", "UC_CostCenter"), "Source", "SP", "Edit", false));

       //loop through calendar weeks collection
        ForAll(colCalendarWeeks,
            //get actual hours into a collection - this method used as there are over 2000 items
            Collect(colActualHours, AddColumns(Filter(List_ActualHours, AH_CalendarWeek = CW, AH_CostCenter = CostCenter),"AH_Value", Value));
            //loop through users and collect the kurzarbeit
            ForAll(colUsers,
                If(
                    IsEmpty(Filter(Data, SP_Woche = CW, SP_Typ = "Kurzarbeit", SP_MitarbeiterID = UE_ResourceId)),
                    Collect(
                        colActualHours,
                        {
                            AH_Value: Value,
                            AH_CostCenter: UE_CostCenter,
                            AH_Source: "KUG",
                            AH_FTE: 0,
                            AH_CalendarWeek: CW,
                            AH_ResourceId: UE_ResourceId,
                            AH_ProjectId: "Kurzarbeit",
                            AH_WorkOrder: "Kurzarbeit"
                        }
                    ),
                    Collect(colActualHours,
                        AddColumns(
                            RenameColumns(
                                GroupBy(
                                    Filter(Data, SP_Woche = CW, SP_Typ = "Kurzarbeit", SP_MitarbeiterID = UE_ResourceId),
                                    "SP_Woche",
                                    "SP_MitarbeiterID",
                                    "Kurzarbeit"
                                ),
                                "SP_Woche", "AH_CalendarWeek",
                                "SP_MitarbeiterID", "AH_ResourceId"
                            ),
                            "AH_Value", Value,
                            "AH_CostCenter", UE_CostCenter,
                            "AH_Source", "KUG",
                            "AH_FTE", Sum(Kurzarbeit, SP_Stunden)/40,
                            "AH_ProjectId", "Kurzarbeit",
                            "AH_WorkOrder", "Kurzarbeit"
                        )
                    )
                )
            )
        );

        //loop through users and add rows for holidays and kurzabeit
        ForAll(colUsers, 
            Patch(colResource, Defaults(colResource), {UC_Resource: UE_ResourceId, UC_Email: UE_Email, UC_Name: UE_Name, UC_CostCenter: UE_CostCenter, UC_Desc: "Urlaub", UC_WorkOrder: "Urlaub", UC_Project: "Urlaub", Source: "ERP"}); 
            Patch(colResource, Defaults(colResource),{UC_Resource: UE_ResourceId, UC_Email: UE_Email, UC_Name: UE_Name, UC_CostCenter: UE_CostCenter, UC_Desc: "Kurzarbeit", UC_WorkOrder: "Kurzarbeit", UC_Project: "Kurzarbeit", Source: "KUG"})
        );
    );
    
    ClearCollect(colNewTable, AddColumns(colResource, "ActualHours", Filter(colActualHours,AH_ResourceId = UC_Resource, AH_WorkOrder = UC_WorkOrder)));
    ClearCollect(colFinalTable, AddColumns(colNewTable,"DATA", Sum(ActualHours, AH_FTE)));
    RemoveIf(colFinalTable, DATA = 0, UC_WorkOrder = "Kurzarbeit");
    RemoveIf(colFinalTable, IsBlank(DATA), UC_WorkOrder = "Urlaub");
    UpdateContext({showLoading: false});
);

Any help would be very much appreciate. I am somehow stucked down in a rabbit hole.

[WiZey · Accepted solution]

Hello @totti50189 ,

Unfortunately, I can't help much on the performance side. I had the same issue where I had to compute statistics from a massive database, and the process took more or less 10 minutes despite trying to optimize as much as possible with "Concurrent()".

However, to avoid ruining the user's experience, I added a fun loading screen with a few animations alongside a loading bar to help alleviate the pain of waiting for something to happen.

The loading bar actually needs you to know the exact maximum amount of data you're expecting to load and require a little bit more of management in your code. Perhaps a rotating gear would suffice, as long as "something is moving" then the user will know with certainty that the app is still working.

Sorry for not being able to help you further. All I can do is recommend you to improve the user experience here, and maybe avoid nesting "ForAll()" inside "ForAll()".

[totti50189]

Hi,

thank you for your reply. It updated the structure to have only one Gallery with several Labels and this helped. Still not perfect but with some user Expirence improvments it is now somehow ok.

Your for all within for all tips was very helpfull.