cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

suggestions for optimizing large datasets

Hi,

 

I am relatively new user to PowerApps and looking for few suggestions on optimizing the performance of the app.

 

We have designed an app with database on SQL Server Express Edition and we have used On-Premise Datagateway for connectivity. We are filtering the results visible to the user based on the user's email address along with filters on other columns:

 

 Filter(Search('[dbo].[CustDocVerification]',TextInputSearchContact.Text,"Branch_Name","Customer_Name",                            "DVU_AssignTo","Acc_Number","Acc_Type","AccOpening_Channel","FTR","CIF_ID","TrackerID"),DVU_AssignTo=gvCurrentUser.Email||(gvCurrentUser.Email = EmailAdmin.Text||gvCurrentUser.Email = EmailAdmin2.Text),AccOpening_Channel<>"Matm - paperless",test<>"YES",FTR<>"No",FTR<>"Yes"

 

There is one main data table ([dbo].[CustDocVerification]) and other supporting data tables used in drop down lists. The supporting data tables are relatively small with max records of 20 rows. The main data table is growing everyday and it has now crossed 3500 records.

The app is being used by approx 10 users (concurrently)

 

 

In the first two weeks of the app, it worked perfectly fine. Now in last few days, users are complaining about slowness in the performance. The gallery is taking time to load and drop downlists are not getting updated as they should be. This is resulting in lot of disatisfaction while using the app.

 

Any help to optimize the app's performance would be highly appreciated.

 

Thanks,

Vivek

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: suggestions for optimizing large datasets

Hi ,@vranjan22,

 

Caching the lookup tables will also help alot.  Since they are rarely changed, this can be done once at the start of the session by doing this in the OnStart property of the first screen. The new Concurrent function will minimize the time needed to create the collections when your app starts. See:

https://baizini-it.com/blog/index.php/2018/01/11/powerapps-caching-lookup-tables-to-improve-performa...

https://powerapps.microsoft.com/en-us/blog/enjoy-faster-startup-times-with-the-new-concurrent-functi...

 

 

View solution in original post

4 REPLIES 4
Highlighted
Super User
Super User

Re: suggestions for optimizing large datasets

Hi Vivek,

 

Try changing your Filter and Search around so it looks like the below.

 

Search(Filter('[dbo].[CustDocVerification]',DVU_AssignTo=gvCurrentUser.Email||(gvCurrentUser.Email = EmailAdmin.Text||gvCurrentUser.Email = EmailAdmin2.Text),AccOpening_Channel<>"Matm - paperless",test<>"YES",FTR<>"No",FTR<>"Yes"),TextInputSearchContact.Text,"Branch_Name","Customer_Name","DVU_AssignTo","Acc_Number","Acc_Type","AccOpening_Channel","FTR","CIF_ID","TrackerID")

 

The query should then filter the entire recordset BEFORE performing the search. I think currently what is happening is it is performing the search first on all the records and then filtering the results which might explain the drop in performance.

 

Hope that helps.

 

Lee


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

Highlighted
Super User
Super User

Re: suggestions for optimizing large datasets

Hi ,@vranjan22,

 

Caching the lookup tables will also help alot.  Since they are rarely changed, this can be done once at the start of the session by doing this in the OnStart property of the first screen. The new Concurrent function will minimize the time needed to create the collections when your app starts. See:

https://baizini-it.com/blog/index.php/2018/01/11/powerapps-caching-lookup-tables-to-improve-performa...

https://powerapps.microsoft.com/en-us/blog/enjoy-faster-startup-times-with-the-new-concurrent-functi...

 

 

View solution in original post

Highlighted
Helper III
Helper III

Re: suggestions for optimizing large datasets

Thank you @Drrickryp

 

 

Caching the list data tables concurrently has significantly improved the performance of the app.

 

Appreciate your help.

 

Thanks,

Vivek

Highlighted
Super User
Super User

Re: suggestions for optimizing large datasets

Hey Vivek.

 

Glad to hear the caching has helped. We also found that using addcolumns to do the lookups (even directly in the gallery:items property) performs better than doing lookups on the labels in the gallery. Give it a try and let me know if this works for you.

 

Have a great day!

Helpful resources

Announcements
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

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

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