cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jesenavaranjan
Helper V
Helper V

Refresh(SqL database) taking ages

Refresh (SQL Database) in my Power App needs to happen once I have made an edit to the database,

This refresh is unavoidable when moving from my gallery-> my edit screen(clearcollect)-> my gallery-> back to edit page which isn't ready in time.

and it does not finish in time, hence appearing to the user as if the change has not been made.

I am using collections where possible. But I can't use collections everywhere (e.g 20,000 rows of data in some tables- and I want the app to be scalabale)
The app looks like it's not working due to this issue.

Jese

7 REPLIES 7
v-xida-msft
Community Support
Community Support

Hi @jesenavaranjan ,

Could you please share a bit more about your issue?

Do you mean that it take a long time to refresh your SQL Table data source within your app?

 

Please consider take a try to turn on the "Explicit column selection" option within Advanced settings of App settings of your app, then re-publish your app, check if the issue has been improved.

 

Also please take a try with the following app performance tips, then check if the issue has been improved:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/performance-tips

 

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.
Anonymous
Not applicable

Thanks for posting in the community @jesenavaranjan - can you review the above reply and update the thread when you have a chance?

 

Thank you,

 

@Anonymous 

seadude
Memorable Member
Memorable Member

Hi @jesenavaranjan .

Some other options:

  • Up the DTU's to 20+ (if Azure SQL db). I've noticed this makes a SIGNIFICANT difference.
  • Use SQL views to simplify the call as much as possible
  • Use DropColumns to limit the data returned to JUST the columns you need
  • Ensure your SQL db is properly indexed and optimized
  • Ensure your internet connection is not throttled (use Speedtest.net or Windows 10 network test) or otherwise limited

The only time I have this type of lag is when trying to pull back image-type data from SQL. Changing to Azure Blog Storage can speed things up too.
Good luck and let us know what you land on.

drewski
Helper I
Helper I

Was a real solution ever figured out?

I'm running into the same issue on my app using On-Prem SQL.

 

We submit an item, go back into said item from gallery, and it's still displaying the old data.

 

When we go navigate back and then back into the same item, it loads fine.

 

Any ideas?  This is preventing us from releasing to a wide audience.

Anonymous
Not applicable

Hi @drewski have you tried any of the options suggested by @seadude above?

 

@Anonymous 

  • Up the DTU's to 20+ (if Azure SQL db). I've noticed this makes a SIGNIFICANT difference. - WE USE ON-PREM SQL
  • Use SQL views to simplify the call as much as possible - YES WE USE SQL VIEWS TO DISPLAY THE DATA
  • Use DropColumns to limit the data returned to JUST the columns you need - NOT SURE EXACTLY WHAT THIS MEANS, WE ONLY PULL ABOUT 7 FIELDS INTO A COLLECTION.. AGAIN IT'S DISPLAYING THE CORRECT DATA JUST FINE IF YOU OPEN THE ITEM A SECOND TIME.
  • Ensure your SQL db is properly indexed and optimized - YES, WE HAVE INDEXES SETUP
  • Ensure your internet connection is not throttled (use Speedtest.net or Windows 10 network test) or otherwise limited - NON ISSUES IN OUR ENVIRONMENT.

 

seadude
Memorable Member
Memorable Member

Feels like your refresh() Function is OnVisible of the screen.

Are you applying it directly after the SQL Patch()?

Another consideration:
- Are you patching large binary files? (PDF, Images, etc.)
- Are you pulling binary data into the collection from SQL?

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,745)