cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jernejp
Helper IV
Helper IV

Need help with optimising database queries

Hi,

 

I'm building an app that would be used for work-related trips by employees of our company. 

This is the database schema:

dbschema.PNG

 

So what I do here is I get the last trip taken by the employee using the application, figure out if this is still the same work trip as before (if it wasn't finished), and finishes the trip if it was started on a different day (other than today), and also sets the current user (by getting info from O365).

 

Set(
    LastTrip;
    Last(
        SortByColumns(
            Filter(
                '[dbo].[trip]';
                user = Office365Users.MyProfileV2().mail
            );
            "user";
            Descending
        )
    )
);;
Set(
    TripID;
    LastTrip.id
);;
If(
    IsToday(LastTrip.start) && IsBlank(LastTrip.end);
    Set(
        Trip;
        LastTrip
    ) && Navigate('Next Screen');
    Navigate(Start)
);;
UpdateIf(
    '[dbo].[trip]';
    !IsToday(LastTrip.start) && IsBlank(LastTrip.end) && id = LastTrip.id;
    {end: now}
);;
Set(
    CurrentUser;
    LookUp(
        Employees;
        Employee_E_Mail = Office365Users.MyProfileV2().mail
    )
)

My question is - all of this code is taking super long (about 30 seconds or even more), which is too much. The code triggers when you press the "Start APP" button, and then the user has to wait for at least 30 seconds. 

Can this be optimised somehow? I know Filter can't be delegated - do I have any other options?


Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
yashag2255
Dual Super User II
Dual Super User II

HI @jernejp 

 

Can you try to use User().Email instead of  using Office365Users.MyProfileV2().mail throughout your formula? Office365 connector usually takes some time to return the data.
 
Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

4 REPLIES 4
yashag2255
Dual Super User II
Dual Super User II

HI @jernejp 

 

Can you try to use User().Email instead of  using Office365Users.MyProfileV2().mail throughout your formula? Office365 connector usually takes some time to return the data.
 
Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

Yeah I agree with @yashag2255 - those 365 lookups are a waste if you're only getting the current user - PowerApps has its own functionality for that, so you could re-write your code like this:

 

// Setting it to a local variable, makes it even faster!
// That way it doesnt even have to use the PowerApps function everytime you ask for it
Set(CurrentUserEmail; User().Email);;

Set( LastTrip; Last( SortByColumns( Filter( '[dbo].[trip]'; user = CurrentUserEmail ); "user"; Descending ) ) );; Set( TripID; LastTrip.id );; If( IsToday(LastTrip.start) && IsBlank(LastTrip.end); Set( Trip; LastTrip ) && Navigate('Next Screen'); Navigate(Start) );; UpdateIf( '[dbo].[trip]'; !IsToday(LastTrip.start) && IsBlank(LastTrip.end) && id = LastTrip.id; {end: now} );; Set( CurrentUser; LookUp( Employees; Employee_E_Mail = CurrentUserEmail ) )

 

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


v-xida-msft
Community Support
Community Support

Hi @jernejp ,

Do you want to improve your formula's performance?

 

Based on the needs that you mentioned, I think it is not necessary to add a Office 365 Users connector within your app, instead, you could use the User().Email function to achieve your needs.

 

I have made a test on my side, please consider modify your formula as below:

Set(
    LastTrip;
    Last(
        SortByColumns(
            Filter(
                '[dbo].[trip]';
                user = User().Email            /* <-- Change formula here */
            );
            "user";
            Descending
        )
    )
);;
Set(
    TripID;
    LastTrip.id
);;
If(
    IsToday(LastTrip.start) && IsBlank(LastTrip.end);
    Set(
        Trip;
        LastTrip
    ) && Navigate('Next Screen');
    Navigate(Start)
);;
Concurrent(                         /* <-- Change formula here */
UpdateIf( '[dbo].[trip]'; !IsToday(LastTrip.start) && IsBlank(LastTrip.end) && id = LastTrip.id; {end: now} ); Set( CurrentUser; LookUp( Employees; Employee_E_Mail = User().Email /* <-- Change formula here */ ) )
)

Please consider take a try with above formula, then check if the issue is improved.

 

In addition, there are also some performance tips with formula in PowerApps, please check the following article for more details:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/performance-tips

 

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks everyone, the Office365 connector was the thing that was slowing it down. I've also used concurrent like @v-xida-msft mentioned.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (20,395)