Need help with optimising database queries

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi,

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

This is the database schema:

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

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

Verified answer

yashag2255 24,769 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

HI @Anonymous

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!
iAm_ManCat 18,256 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
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 ) )
v-xida-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous ,

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 Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

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