cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Dual Super User III
Dual Super User III

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

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

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

 


@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


Highlighted
Community Support
Community Support

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
Helper IV
Helper IV

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
Check this Out

Announcing

Class of 2020- Season 2

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,598)