cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vranjan22
Advocate II
Advocate II

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
Drrickryp
Super User II
Super User II

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
LeeHarris
Solution Sage
Solution Sage

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

Drrickryp
Super User II
Super User II

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

Thank you @Drrickryp

 

 

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

 

Appreciate your help.

 

Thanks,

Vivek

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

Top Solution Authors
Top Kudoed Authors
Users online (97,210)