Performance issue office365 with addcolumns

[Pieter3]

I made a employee library where columns had to be added. After this was added, the performance has decreased and now it takes 3 minutes to start up.

Does someone have experience with this performance issue and know how to fix this?

Code: 

Sort(
AddColumns(
AddColumns(
(Filter(
AddColumns(
Filter(
AddColumns(
If(
Dropdown1.Selected.Result = Blank();
Filter(
AddColumns(
Filter(
'Office365-gebruikers'.SearchUser({searchTerm: "";top: 999});
AccountEnabled = true
);
"filterKolom";
JobTitle & "|" & DisplayName
);
naamFunctie.Text in filterKolom
);
Filter(
Filter(
AddColumns(
Filter(
'Office365-gebruikers'.SearchUser({searchTerm: "";top: 999});
AccountEnabled = true
);
"filterKolom";
JobTitle & "|" & DisplayName
);
naamFunctie.Text in filterKolom
);
Department = Dropdown1.Selected.Result
)
);
"skills";
Concat(
'Office365-gebruikers'.UserProfileV2(Id).skills.Value;
Value;
" "
)
);
naamFunctie_3.Text in skills
);
"responsibilities";
Concat(
'Office365-gebruikers'.UserProfileV2(Id).responsibilities.Value;
Value;
" "
)
);
naamFunctie_2.Text in responsibilities
));
"birthday";
'Office365-gebruikers'.UserProfileV2(Id).birthday
);
"birthdayNext";
If(
(Month(birthday) > Month(Now())) Or ((Month(birthday) = Month(Now())) And (Day(birthday) >= Day(Now())));
Year(Now());
Year(Now()) + 1
) & "-" & Month(birthday) & "-" & Day(birthday)
);
birthdayNext
)

[zmorek]

I'm struggling to see what your attempting to create with the formula, but the performance issue is because it's doing a ton of calls to a ton of places and trying to temporarily store that data in multiple columns. Is this creating a gallery of data containing all data about all employees?

[Pieter3]

Yes, The formula is in a gallery and displays employees and there data and you can filter on that through a text field and combo boxes.

[zmorek · Accepted solution]

Part of me wants to suggest using a collection of that data, or using a With() formula, vs. always querying the Office365-gebruikers connector (is that Office365Users?). Also setting the current date to a variable (as opposed to calling Now() all the time). Each time you ask PowerApps, "what day/time is Now?" it will do a fresh query to figure out what Now() is. Consider setting a variable OnStart of your app to house that information then use that variable in the formula.

Think of it this way - PowerApps will re-evaluate each portion of an If() statement, and if you query a data connector in each portion of the If() statement, that's two queries it has to run. Then if you add columns to each of those portions of the formula, it has to regenerate all those columns again. Then as your conditions for that If() statement change, it re-evaluates that statement again and again (causing your app to lose responsiveness). To add on top of it, PowerApps doesn't always evaluate sequentially (meaning the top part of your formula doesn't always evaluate first, then second, etc); it tries to do what it can to evaluate things at the same time.

Look for example at the first portion of your formula. I've underlined two portions of the formula that are exactly the same, the only difference being that you want to filter by department if the dropdown isn't blank (excuse me if I accidentally highlighted something a little bit incorrect, I don't know Dutch [?] that well):

If(//condition 1
Dropdown1.Selected.Result = Blank(); 
Filter(
AddColumns(
Filter(
'Office365-gebruikers'.SearchUser({searchTerm: "";top: 999});
AccountEnabled = true
);
"filterKolom";
JobTitle & "|" & DisplayName
);
naamFunctie.Text in filterKolom
);
Filter(//condition 2
Filter(
AddColumns(
Filter(
'Office365-gebruikers'.SearchUser({searchTerm: "";top: 999});
AccountEnabled = true
);
"filterKolom";
JobTitle & "|" & DisplayName
);
naamFunctie.Text in filterKolom
);
Department = Dropdown1.Selected.Result

You might be able to shorten it up with something like:

With(    
{wDataSource: 
    Filter(
        AddColumns(
            Filter(
                'Office365-gebruikers'.SearchUser({searchTerm: "";top: 999});
                AccountEnabled = true
            );
        "filterKolom";
        JobTitle & "|" & DisplayName
    );
        naamFunctie.Text in filterKolom
       )
},
If(
    Dropdown1.Selected.Result=Blank();
    wDataSource;
    Filter(wDataSource, Department = Dropdown1.Selected.Result)
)
)

Excuse my terribly-formatted snippet above, I typed it free-hand. But what that does it create a record that you can simply call over and over within that With() statement, but less verbose. Simplify formulas with the With function | Microsoft Power Apps

I don't know 100% if something like this is what you need, however, I think it's worth considering. I don't have a hard-and-fast solution, but I hopefully helped spark some ideas!

[Pieter3]

Thank you for your time @zmorek ! It worked for this part and now I'm gonna try it with the rest of the code. Again thank you.