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

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

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!

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
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (2,289)