cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jernejp
Level: Powered On

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
Dual Super User
Dual Super User

Re: Need help with optimising database queries

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
Dual Super User
Dual Super User

Re: Need help with optimising database queries

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

Super User
Super User

Re: Need help with optimising database queries

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 ) )

 




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!
Community Support Team
Community Support Team

Re: Need help with optimising database queries

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.
Highlighted
jernejp
Level: Powered On

Re: Need help with optimising database queries

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,014)