Refresh(SqL database) taking ages

(1) Share

Report

Posted on by jesenavaranjan

227

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

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Sort by

v-xida-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

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,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
seadude 1,855 on at

Like
a
(1)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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

@Anonymous

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
seadude 1,855 on at

Like
a
(0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No