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.  

[Mr-Dang-MSFT]

@martinav I think you have the right idea here to create a SQL View to 'inner join' the data you would have retrieved as a look up. Then bind the gallery to the View. This pattern eliminates the need for any lookup (you might need one at the time of patching, but not while reading).

When? I always do it this way. 

On the topic of collections, I would only collect if you are having significant perf issues and not many changes are made to the data. Here's some more recommendations:

https://powerapps.microsoft.com/en-us/blog/performance-considerations-with-powerapps/

[mogulman]

I use SQL Views for Galleries since I only need a few fields for Gallery and all of them require fields from other tables.  I spent several days running SQL Profiler to see what PowerApps was doing and measuring performance.  I saw that I could cut down Gallery queries by 15-20 times by using ClearCollect.  The user has to refresh collection when they add a new record or change name on record.  In my case users add about 4-5 records a day.   I also have 23 lookup tables that almost never change.  I initialize all collections on OnStart.  All my Detail forms are SQL Views.  I don't use collections on those views since they have lots of fields.

If you are using on-premise SQL Server I suggest using SQL Profiler to understand app performance.

[martinav]

@Mr-Dang-MSFT,

Yes, I am using SQL Views quite heavily.  I noticed my SQL server is running on a VM.  It is currently setup with 2 cores and 16GB.  When making calls, it looks like CPUs max out, and I do see the memory creeping up to 12GB at times.  

Do you think increasing resouces on the VM might help? 

I can also run traces now with the SQL Profiler.  Any help there to know what to look for?

[mogulman]

I'm no expert on SQL Server but in the testing I've done an issue with PowerApps and memory.  It is performing simple queries.  Memory issues happen when you do complex queries on large datasets.

You'll need to use Profiler in off hours.  What I do in Profiler:

• Connect to database
• Get App setup to perform some function like open app.
• Pause Profiler and erase history.
• Unpause Profiler
• Open app
• Observe profiler
• Pause Profiler as soon as app has opened.
• Go back through profile and observe time spent in queries.  You'll see time in a column.  

I profiled dozens of actions and kept a spreadsheed of times.  You need to run action several times to get a good feel for it.  I started changing app and measuring changes in performance for different actions.  One interesting thing I noticed that when form intialized it would perform query on DB even though ID was 0.  I added code in app to not do this.  100's of little things.

I spent days doing this to get an understanding of PowerApps

[Mr-Dang-MSFT]

@mogulman, this is a very good tip on understanding how SQL and PowerApps work together. As every app's needs are different, profiling will get a more precise answer on requirements.

The community would benefit a lot if you could write a blog post on using SQL Profiler with screenshots. @Anonymous can help you get started.

https://powerusers.microsoft.com/t5/PowerApps-Community-Blog/bg-p/PowerAppsBlog

[martinav]

@mogulman,

I have been using the Profiler for a while.  It is quite telling...  I have an app that provides a list of drawings based on search criteria.  I limit results to 100 items, which is reasonable for the type of search that it does.  I have a single lookup into a nested SQL View.  

LookUp('[dbo].[VIEW_ALL_PDM_COMBINED_LATEST]',PartNumber=(MD_PartNumber),CurrentStatusID)

When I trigger the gallery to refresh that view... it generates over 23,000 lines of code!!! (Doing a copy from PRofiler, and paste into excel).  I do not notice errors on the SQL side, but, it does take a long time too populate the gallery.  The information it provides is important, but secondary to the search.  So, the table populates, and the information from this lookup updates over the next 10 or so seconds.  

Unfortunately, the SQL View has way too many lines to pull it into collect() first.  It exceeds the delegation rule.  

Before I even "run" the app in the editor, this command shows a server error.

What is strange, is that this command actually works without giveing erroneous results.  No apparent SQL errors either.  

I am wondering if there is some way to "mirror" sql tables to Azure, then do my lookups to the SQL table there.  However, I would need Azure to update each time the parent SQL table changed, or something was added new.  I had thought about getting a trial account going there, but I want everything lined up first, so I have it working long enough to give it a fair try.  

[PaulD1]

If you are using Azure SQL DB you can use the Extended Events to see the SQL Statements generated by PowerApps. It is a while since I last did it, so can't give detailed steps, but I did get this working albeit there was a lot of info to sort through to find the SQL Statements being executed.

[Mr-Dang-MSFT]

---

@martinav wrote:

@mogulman,

 When I trigger the gallery to refresh that view... it generates over 23,000 lines of code!!! (Doing a copy from PRofiler, and paste into excel).  I do not notice errors on the SQL side, but, it does take a long time too populate the gallery.  The information it provides is important, but secondary to the search.  So, the table populates, and the information from this lookup updates over the next 10 or so seconds.  

---

@martinav, when you place a lookup to SQL inside of a gallery that is already directly connected to SQL, you're making repeated calls to SQL that way. You can significantly reduce that by creating a view that already has all the fields you need by joining the right tables and fields.

When you create the new View, can you run the profiler again?

[martinav]

@Mr-Dang-MSFT,

Ok... I get what you are saying.  My issue... part of my data is somewhat static.  I collect that data, and it gets automatically refreshed every 15 minutes.  The other data in the table is much more dynamic.  I need that to be real-time update.  Thus, the lookups.  It takes a long time to collect() the data, and the live data comes from tables that are over 4,000 lines.

I dont know that perhaps I can have a refresh that strips the new sql rows and modified entries, and then join those tables already in a collection?  I think that would take much less time than trying to pull in the entire dataset.