Data Table stuck on "Loading Data"

[pitr]

PowerApp has multiple data tables that leverage the same source of data.

Data is stored in an Azure SQL Database.

The data is first filtered and then sorted.

Each table shows a different subset of the data.

Expectation

Users should be able to scroll through an entire list of results, in this case only about 700 rows.

Experience
When scrolling, we find that frequently (but not consistently) the data decides to not load.  I've experienced the same, even coming back several minutes later only to find that there's been no progress.

The bottom of the data table reads "Loading Data".

This does not happen at the same point each time.

Sometimes refreshing the data source helps so we've ended up adding a button, but ideally the users shouldn't notice the loading.

How can we improve the reliability and performance of the data table?

[Mr-Dang-MSFT]

Hi @pitr,

For issues affecting the flow of data in an app, it's helpful to include a session id:

https://aka.ms/pa/sessionid

Can you share more about your setup? Are all the datatables on the same screen?

Do their filters have dependencies against other controls? Where are those controls? A screenshot can help with these two questions as well.

[v-xida-msft]

Hi @pitr,

How many records existed within your Azure SQL table? More than 2000?

Could you please share a bit more about the formula you typed within the Items property of the Data Table control?

Currently, I could not reproduce your issue on my side. As an alternative solution, I think the Timer control could achieve your needs.

I have made a test on my side, please take a try with the following workaround:

Add a Timer control within your app, set the Duration property to following:

1000

Set the Repeat property and AutoStart proeprty to following:

true

Set the OnTimerEnd property to following:

Refresh('YourAzureSQLTableDataSource')

Set the Visible property of the Timer control to following:

false

In addition, if the number of records in your Azure SQL Table is not more than 2000, you could consider take a try to save your Azure SQL Table records into a Collection within your app, then use the Collection as data source within your Data Table control. Please take a try with the following workaround:

Set the OnVisible property of the first screen to following:

ClearCollect(RecordsCollection, 'YourAzureSQLTable')

Set the Items property of the Data Table to following:

SortByColumns(
                          Filter(
                                   RecordsCollection,
                                  'Filter Condition'
                          ),
                          "SortColumn"
                           SortOrder.Ascending
)

Please take a try with above solution, check if the issue is solved.

Best regards,

[pitr]

Session ID: 0722e960-af83-4a08-a11e-e4c42d195d79
PowerApps 3.19021.9

This happens both in the editor and when running the app.

All three datatables are on the same screen, yes.

The only dependency I see is a context variable that is used to select the sort direction.

Sort(
Filter('[FrontEnd].[ProductionState_Current]',State_x002e_ID=3),
IPAddress,
If(dtMonConInProdSortAscending,SortOrder.Ascending,SortOrder.Descending)
)

[pitr]

At the moment the table in SQL has only 947 records.

There are three data tables referring to the SQL table, each pulling a different subset of the data.

If Timer is used, will it maintain the scroll position within the datatable or will the scroll be reset?

[Mr-Dang-MSFT]

Is this data located on SQL Azure or SQL on premises?

[pitr]

SQL Azure.

[Mr-Dang-MSFT]

It is possible you may be hitting a DTU limit on SQL Azure then. How many other locations in the app are making direct references to SQL? If there are too many at once, they will max out your allowable DTUs and you will not see receive data in places you would expect.

[pitr]

I'm only seeing spikes of 13% of my DTUs.
There's a few more locations, yes, but they're different tables.  So far it's this one data source that we're experiencing failures on.

[Mr-Dang-MSFT]

I've notified the team relevant to the data table control.

Can you run this test for me:

1. Create a blank app
2. Insert 3 data tables
3. Bind each data table to another SQL table. Make them all the same table.
4. Observe.

Let me know if the experience is any different.