Answered

Editing single row in a gallery of ~800 very slow

(0) Share

Report

Posted on by WheresWally

Hi All,

I am using a collection as a source for a gallery set up similar to an excel table. All the controls in the gallery are read only labels and only a a few have text formatting around the ThisItem value (like dates or $ etc) and 2 or 3 are lookups to other small collections.

I have input fields to the side which show the selected gallery items data as defaults and allow the user to modify them and click a save button to change that row. I do this via an UpdateIF on the collection based on the ID of the selected row. This updateif is very fast if I've filtered the gallery down to 1 row. BUT if I have the ~800 rows in, it can take up to 14 seconds to regain control of my gallery.

Is there a way to update that one row in the collection and have it update visibly in the gallery without this kind of delay? It is making the app unacceptable by end users.

Thanks for any advice.

@WarrenBelz you are the MVP I'm hoping might see this 🙂

Categories:

Building Power Apps

All responses (8)

Answers (1)

Sort by

Gerard Simpson 2,987 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

Hi, are you saving to the Datasource and recollecting all the data again upon saving a record? - this would slow things up. I would contest you don't need to collect that many rows into app memory - if possible try working with a direct connection between your gallery and the data source, which provides an optimised load out of the box.

I would also avoid lookups inside a Gallery. You have mentioned 2 or 3 are lookups - i.e. a lookup would need to be perform 3 x ~800 times every time you do anything that causes a gallery refresh or if you leave and come back to that screen. This is a lot of heavy lifting for your app. If you really need that lookup data and you really need to use collections, you could tag those as additional columns onto the collection when creating it - or if they are small collections would it be possible to morph them / make them part of your data source and use the collections as choice lists. Sorry I don't know your exact use case, but hopefully you find some of this.

I would start with removing the lookups as some initial troubleshooting to see if that moves the lever and go from there.
WheresWally 58 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks @AmDev , really appreciate you taking the time to answer.

I will try removing the lookups.

It is somewhat of a requirement to show all 800 rows for them to scroll through and assess yes.

I was under the impression that collections were much faster to work with than connecting direct (in our case) to an on prem SQL server (via a gateway). That being because the collection is 'in memory'. I'm not recollecting at all in the save step. While I do patch the row to the source database, I'm not querying it again, but instead am using an UpdateIf on the one row in the collection - thinking of it as the fastest way.
WheresWally 58 on at

Like
a
(0)

Report
Copy link

Link copied!

I removed all the lookups and formatting so that every column in the gallery is just plain ThisItem.
I also removed the patch to the SQL database and so am just running the UpdateIF on the local collection. Still seeing the same amount of delay.

With one row in the gallery, it's basically instant. With 800 rows in the gallery, the UpdateIF on the single row takes about 10-14 seconds before you regain control of the gallery.
WheresWally 58 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi again, I've tested switching the source of the gallery to the SQL connection directly - no collections involved at all. After patching the row in the SQL table (very fast) I am using a Refresh() on the SQL data source so the gallery updates with my change. This is even slower to regain control of the grid (towards 20 seconds in total).

If this method had a 'retrieve and update only the single row I specify with an ID in the gallery' option, I think it would maybe work. But I don't think that's a thing right?
WheresWally 58 on at

Like
a
(0)

Report
Copy link

Link copied!

To answer my own question I think...

This post seems to suggest it's not possible:
https://powerusers.microsoft.com/t5/Building-Power-Apps/Gallery-refresh-one-or-few-records-only/m-p/929217/highlight/true#M294973

This idea submitted seems to outline the issue clearly:
https://ideas.powerapps.com/d365community/idea/9bee640d-7717-4599-bb63-5b7f36ad3b1e

Also, I tried using a virtual table as my gallery source instead. No noticeable change in refresh speed. Figured as much but thought it worth a shot.
Gerard Simpson 2,987 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WheresWally - would this setting help you?

It's in preview, but if it helps and the lag is currently bad, I would go for it.
WheresWally 58 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks AmDev, I am using SQL Stored Procedures in the PowerFX bar in other areas of the app (submitting a lot of selected row ID's for a batch edit for example). So that setting is currently on. What I'm finding is that even when removing the SQL patch step - so just doing the UpdateIf on the collection feeding the gallery for the one row, it reloads all 800 rows in the gallery and does it very slowly.
Verified answer

WheresWally 58 on at

Like
a
(0)

Report
Copy link

Link copied!

In case anyone comes along with the same issue, here is what I've done instead:
1. Shifting the Filter out of the Items property: With around 20 columns and many of them needing filtering, the Filter around the collection in the Items property of the gallery was the main source of the delay. I moved the filtering mechanism to a hidden button by making the onchange of each filter press that button which does a clearcollect on a SECOND copy of my original collection. This processes all filters at once every time and trims the collection feeding my table. The clearcollect is very fast and I still have an unchange copy of the original collection without needing to query SQL again until the user changes the month filter to bring in a different ~800 records.

2. Switched to a Data Table instead of a gallery. Seems backwards I know. BUT now that I have a second collection, I don't need checkboxes on each row to select them for batch editing. I can just use the ID numbers of the collected rows once the user has finished filtering. I'd already moved all editing of a single row out to a 'form' type panel and using the DataTable.selected of the datatable control works fine for that.

So this has solved the delay in single row editing which is down to about 1 second, and it still allows for batch editing. The other reason for shifting to data table is that it COMPLETELY solves the scroll lag issue which was plaguing my gallery. Thank goodness. There are some cons, but really none that make it worse than it was before.