PowerApps to SharePoint performance issue

[SMurphy]

Hi I have been trying to pin point the issues i am having with my app, the app is starting to load quite slowly. The main Screen of the app is a gallery of a list of projects (currently about 1700 records).  The code for the items of the gallery are as follows: 

With(
   {
      wProject:
      Filter(
         tblProject_Team_Alignment, 
         Len(cmbWorkingGroup.Selected.Result) = 0 ||
         Primary_Working_Group.Value = cmbWorkingGroup.Selected.Result
      ).'Project#',
      wData:
      Filter(
         tblProjects,
         (
            Toggle1.Value ||
            (
               Len(Portfolio_Status.Value) = 0 ||
               Portfolio_Status.Value = "Active"
            ) 
         ) &&
         (
            Len('cmbProject#'.Selected.Result) = 0 || 
            'Project#' = 'cmbProject#'.Selected.Result
         ) &&
         (
            Len(cmbOpportunityType.Selected.Result) = 0 || 
            Opportunity_Type.Value = cmbOpportunityType.Selected.Result
         )
      )
   },
   Filter(
      wData,
      'Project#' in wProject &&
      (   
         Len(cmbProjectOwner_1.Selected.Result) = 0 ||
         Project_Owner.DisplayName in cmbProjectOwner_1.SelectedItems.Result
      )
   )
)

I am curious if this code needs modifications and possible modify the gallery to not load data until at least one filter is selected, would this help performance? 

As well within the gallery one of the display items labels is a lookup to a sharepoint lookup and i would not be surprised that this is causing issues but i am not sure on a work around. 

LookUp(

  tblProject_Team_Alignment,

  'Project#' = ThisItem.'Project#'

).Primary_Working_Group.Value & " - " & ThisItem.Project_Name

Any recommendations or help would be very appreciated, thanks!

[Amik]

@SMurphy - have you checked if removing the LookUp function from that Label actually improves performance?

Removing the LookUp function you're using on the Label control, and directly inserting it into the formula you're using for the Items property in the Gallery could boost performance.

For example, replace this section:

 Filter(
         tblProjects,
         (
            Toggle1.Value ||
            (
               Len(Portfolio_Status.Value) = 0 ||
               Portfolio_Status.Value = "Active"
            ) 
         )

With:

Filter(
    AddColumns(
        tblProjects,
        "wrkgroup_proj_name",
        LookUp(
            tblProject_Team_Alignment,
            'Project#' = ThisItem.'Project#'
        ).Primary_Working_Group.Value & " - " & ThisItem.Project_Name
    ),
    (Toggle1.Value || (Len(Portfolio_Status.Value) = 0 || Portfolio_Status.Value = "Active"))
)

Then on the Text property of your Label, use:

ThisItem.wrkgroup_proj_name

[SMurphy]

@Amik thank you for your response. The simple thought of removing that code to test to see if it would improve didn't come to mind for some reason, i have tested that and does seem to make a slight improvement but not to the extent that i was hoping.

I did a little more trouble shooting and i believe i may have found two controls that may be cause some delays.

ForAll(Distinct(tblProjects, 'Project#'), {Result: ThisRecord.Value})

This is the code for the cmbProject#, i did this work around to allow the project number which is a short text to be converted to a number for ascending order. 

Sort(ForAll(Distinct(Filter(tblProject_Team_Alignment, IsBlank(cmbWorkingGroupType.Selected.Result)|| false || Primary_Working_Group_Type.Value = cmbWorkingGroupType.Selected.Result),Primary_Working_Group.Value), {Result: ThisRecord.Value}), Result, SortOrder.Ascending)

This is the code that is being used for this cmbWorkingGroup, both these combo boxes are used within the Gallery filters. 

If you could possibly help with these that would be great. Thank you very much!

[Amik]

@SMurphy -

For All can be an expensive function and is often misused/misunderstood.

Regarding the first code, I do not understand why this function is needed for the "cmbProject" ComboBox. I certainly do not see how it will convert a string into a number and then sort that number by ascending order.

Regarding the second code for the "cmbWorkingGroup" ComboBox. If you forget the code for a moment, what results are you trying to return in this ComboBox?

I assume you want to return distinct values of something, based on some other filters, and to return the results in ascending order. Is there a reason you're using ForAll here?

[SMurphy]

I am trying to get the district values of Primary_Working_Group, which is a SharePoint lookup column. I believe i am using the forall because for some reason that is the only way i can get the sort or sortbycolumns functions to work.

For now, in regard to the Project#, i am changing that to a Text Input with delayed input on to prevent a large list of project numbers being collected. 

Thanks. 

[Amik]

@SMurphy - 

Regarding "Project#" - why is using ForAll preferred over:

Sort(
   Distinct(
          tblProjects,
          'Project#'
   ),
   Value,
   Ascending
)

Regarding the "cmbWorkingGroup" ComboBox - is the "Primary_Working_Group" field a multi-select LookUp data type?