Multiple calls to SQL VIEW in Gallary - should I collect() first?

[martinav]

I have a very busy gallary.  I have I think 4 lookups into 2 SQL tables.  So, if I have a large number of items in the gallary, it churns quite a while.  Tables in themselves are pretty cheap, with regards to memory (I think?)  At what point does it make sense to Collect() the table into the app, and then make the calls to the collection?  

I would really like to only do one Collect(), however, the data is dynamic, and the app needs the latest every time the gallary is updated.  I cant let the data go stale.  

[mogulman]

I'm not understanding dynamic data. How can a user even observe 4000 points of data.  PowerApps can do some amazing things but it is not for all applications.  You may need to consider a custom application.

[martinav]

@mogulman,

We have a database of about 2,500 viable current production drawings.  All of which must be available for a search return.  I only allow 100 to be returned at once, and reccomend tightening search requirements to narrow results if trunkating is an issue.  

Dynamic simply from the aspect of having the search results provide status real-time.  Up to this point, there has been nothing keeping me from this.  I am trying to identify methods that might provide better performance.  Reducing the number of hits through the gateway seems to be the bottleneck of performace at this point.  As I learn more, I will employ new methods to speed it up a bit.  

The system is actually up and working.  Other than issues from another post (server error issues, apparently from a recent PA update) nothing is really broken.  I'm always looking for ways to make things better.

[Mr-Dang-MSFT]

To clarify,

• your static table of 2500 is refreshed every 15 minutes (shows 100 at a time)
• the dynamic data of 4000 needs to be real-time--are you getting back 4000 or just accessing 4000?

The first one is easily doable. But if you're getting back 4000 records each time in real-time, that's not going to be performant.

My suggestion is to rework your app experience. Right now you're showing multiple lookups for all 100 records that you return. If you change the experience to only show the lookup data when you click into a record or a subset of records, your app will work very smoothly. Think about the minimum amount of things that needs to be seen.

Another consideration is the data type. Text and numbers are small, but if you have an image column, that's a much heavier load.

[martinav]

@Mr-Dang-MSFT,

I'll show you some pictures.  Sometimes that helps.

My main data is a collect() of all active drawings in our database (a complex join in SQL from four sources into a view).  That has a limit of 4,000 lines due to the method used to collect().  It allows for double the 2,000 item limit.  The current number of returns in the SQL view, and the collection is about 2,500 (noted by the total number value in the top of the image).  The collect() commands take about 20 seconds to load the core data OnStart of the app.  Once loaded, it automatically re-loads every 15 minutes.  The performance of that data in itself is very good, since all of the data being queried is in the collection.

The status column on the right is the challenge.  That is done by a complex combination of three lookups to three different tables.  So, this is real time data.  Each line in the gallary has to make three calls over the gateway.  The "100" is done by a FirstN() command, to limit the results if the search criteria are too broad.  

There is no image data coming across.  All graphics are images that are imported via Media in the app itself.  Just icons to indicate status.  

One way to limit the lookups, is if there is a way to pull the entire row of data in on a single call.  I could make a SQL view that shows just items that are in Revision status, and then call the entire SQL line.  But, I dont know if that can be done in a gallery.  Or, I can do a Collect() every tim the search initiates, but I am concerned about the time that would take, but, that SQL table may only be a hundred lines...  perhaps thats a good way to do it.  I think that would be much faster than the continued lookups over the gateway.

Just thinking a bit there in the last paragraph.  I think this shows a bit more what I'm doing.  You may certainly ahve some other ideas.

I also appreciate the reccomendation to make the view simple.  My intention of this app is to provide a particular user team what they need to see in a single view.  Fewer picks to get data is the goal.  If they need more data, they can pick any line and get a full breakdown of the item.  This is shown below.  This screen does an extremely complex data call, and does take a while to load, and the graphics is a PDF file that is copied into the PA via a trick done with a base64() conversion in flow.  This page load time is acceptable, because its only done once, and for in depth study.  It is a dynamic screen, and you can pick other part numbers on the screen to view its in-depth data.