Improvee a Filter LARGE Sharepoint List by Created Day (without time)

Like (0) Share

Report

Posted on 20 Mar 2021 15:06:01 by OSBSoto

102

Hi,

I'm looking for help to filter a Sharepoint List with over 10,000 rows and growing daily with hundreds more, by the Created 'Day', or whatever it applies, to speed up the loading time and prevent Delegation Warnings.

Explanation of the scenery:

The Evaluations_Details List has over 10,000 rows which consist of the Answers of Evaluation_Points made on several Evaluations.

My Lists are:

Sharepoint List - Evaluations (have nearly 1,000 records to date, and approx 100 new per Week)

ID|Location ID|Supervisor (single line string)|Created By (default column)|Created (default column)

Sharepoint List - Evaluation_Points (have 24 records to date, and nearly no changes)

ID|Active (true/false)|Evaluation_Objetive

Sharepoint List - Evaluations_Details (over 10,000 records and over 100 new records per workday)

Currently, before knowing it would escalate this much, I just used to filter all the responses to location X, but now that there are over 80 different Evaluations in each location, almost 100 different locations, AND every Location considering the 24 mandatories Evaluations_Points that stores in Evaluation_Details a row per each, the app is starting to lag/freeze/taking up to 15 seconds to load any screen.

Considering:

On App.OnStart is filtered the Locations associate with a specific Supervisor
On the First View, a DataGallery shows all the Evaluations created by that person and the user chooses which one to fill / register/read responses
On the Second View, it is possible to VIEW in another DateGallery all the details about the unique inspection selected
On the Second View, also, it is possible to view in a FormViewer has all the information related to each response with a Filter from the first DataViewer.
On the Second View, at OnVissible is where I am currently filtering with this formula/Collection:
ClearCollect(ColeccionDeResponses,Filter(Evaluations_Details,ID_Evaluation = Text(Gallery_Browser_Evaluations.Selected.ID)));
ClearCollect(
ActiveEvaluationsPoints,
Filter(
Evaluation_Points,
Activa = 1
)
)

Also attached the app to this; the app variables are mostly in Spanish due to the language of my inner customers but translate for the purpose of this threat.

Thanks for any improvement possible to this.

Categories:

Dataflows

Using Apps Using Formulas

Gestor de Visita ...

All responses (4)

Answers (0)

RandyHayes 76,287 Super User 2024 Season 1 on 20 Mar 2021 at 19:57:55

Like (1)

Report

Re: Improvee a Filter LARGE Sharepoint List by Created Day (without time)

@OSBSoto
I reviewed your App.

To critique a bit...

1) One of the things I noticed is that you are recollecting data on every screen OnVisible. This is primarily your source of slowness. There is no need to do this and this is one thing that Collections in apps will cause...constant reloading of the collection. And...you ARE constantly recollecting the same information that you already have in your app!

A collection is a static snapshot of a table. In your case you are taking static snapshots of your data table...which is already a table. As long as you can delegate your filter criteria to the datasource, you need not use them at all - especially in this app.

2) You have a fairly decent amount of redundant controls in your app. As well as more variables than an app this size should need.

3) You are not utilizing some of the built-in capabilities of the Editforms for validation and are writing more formulas than necessary.

4) You are reusing groups of controls on each page that should be consider to be moved to a component instead.

The only real delegation issue I see would come from your searching in the Gallery_Browser_Vistas. BUT, that is already pre-filtered from the collection level with delegable criteria. SO...the collection can be removed (as with all of them) and move the prefiltered data lookup directly in the gallery.

In conclusion...yes, you have some serious amount of load, load, load of data. It is not necessary and if you remove all of that, I believe your performance would be tremendously different.

Sorry to beat up so much, but I believe you were looking for a honest critique of the app, as you provided it.

I hope this is helpful for you.
Drrickryp Super User 2024 Season 1 on 20 Mar 2021 at 15:26:40

Like (1)

Report

Re: Improvee a Filter LARGE Sharepoint List by Created Day (without time)

@OSBSoto

Take a look at @WarrenBelz 's blog post here for the most up to date way to deal with delegation in SharePoint. https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/ .
OSBSoto 102 on 20 Mar 2021 at 15:16:44

Like (0)

Report

Re: Improvee a Filter LARGE Sharepoint List by Created Day (without time)

Hi, can't change the data source; Sadly I am to say that "I'm lucky to have at least several licenses of Office 365 Basic" (it's a small family business in the Caribbean Sea)
Drrickryp Super User 2024 Season 1 on 20 Mar 2021 at 15:12:20

Like (0)

Report

Re: Improvee a Filter LARGE Sharepoint List by Created Day (without time)

@OSBSoto

While there are work-arounds, I suggest that you change to SQL or Dataverse if possible to take advantage of the flexibility in those systems with regards to delegatable functions in PowerApps for large datasources. The delays will likely become unbearable as your data grows.