Unanswered

Collections, performance, and right way to manage a reasonable response times?

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi All,

Background... we are an early stage startup... but have a transactional platform... that is pushing tables w/ 1,000

's of records... in my life, this is a very small DB... but I'm bumping into performance issues, 500/2000 fetching limitations, etc.

I had gotten hip to the idea of using collections in the App.onStart()

Concurrent(
ClearCollect( col_Trainers, 'xxx.Trainer' ),
)

Where this is connecting to a PostresSQL db connector... I initially had been of the thought this would localize the whole remote table... but now realize this will only be 500-2000 records (depending on your App settings). I can't find the post at the moment, but found one "creative" approach... to create a number of temp collections in onStart that sequentially take 2,000 item chunks and then stitch them together afterwards.

We use a lot of DataTables... basically a grid screen to form detail screen. I've started using more pre-filters at the top of the grid to minimize fetched rows at any time.

Other thoughts on working w/ or around these performance and record limitations? And I'm definitely left scratching my head as to the value of collections? Maybe for like small lookup tables?

- Marcus

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@timl @v-bofeng-msft given your useful insights on the Filter + As, thought you might have a POV here too?

Was this reply helpful? Yes No
cwebb365 3,294 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Collections are essentials either used for cache (performance by limiting round trips to DB/offline storage) or advanced table variables essentially to manipulate data.

In reality, most people don't use over 500 records in tables and scan through that data. There is always some kind of filter in the workflow that they will be looking for, searching etc. to narrow down results for comparisons. If for some reason they are looking through grid views of hundreds or thousands of records to work from then PowerApps is probably not the tool for the job.

I'm currently building a grid view like app and the performance isn't great due to number of columns. etc. But the returned number of records isn't an issue as the default view for example shows past month entries, since they are always only working on the past period. If they need to change that period or do a filter they can from there or on the all entries screen, just start with nothing and a message saying they need to add a filter to begin. etc.

The other issue you are going to run into the PostresSQL isn't delegable at all, so none of the comparison filtering etc. is going to work baked into PowerApps, so like you said you'll have to get all you're data into collections etc. and I don't know how much is going on in the actual Database concurrently but you could run into issues there if things are updated by multiple people since you will be working on cached versions of the app if you do manage to get everything loaded up into local collections. Any updates you make will have to be patch's, which you could probably do verifications before making those patches that a record has changed yet or not, then make the patch, otherwise show current value, but it will def. make things require a lot more work in that design.

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

Like (0)

Report
Copy link

Link copied!

@cwebb365 right now I'm questioning the value of Collections...

in terms of a UI experience, I'd agree that 500+ records is of limited use

from a DB perspective, however, a table of 500+ would be the norm...

So... my understanding... Collection = local cache for a db... and capped in size (without significant hack) to 500-2000 records... so... take Orders, Clients, Products, or any other typical tables with way more rows than this... where / why / how would I use a collection? I can't preload the Collection in onStart. Maybe one use I could think of... isolate a set of filters at the top of the screen... date, name, status, etc. and then run a ClearCollect when filters update to change the Items of a DataTable or Gallery... then any pagination or sorting of that Layout control wouldn't have to hit DB again?

What else am I missing?

Was this reply helpful? Yes No