Modern application development is about more than just the user interface. Modern applications will connect to a number of APIs and databases. This is true with both web-based and mobile applications. Microsoft has done a great job of simplifying database connectivity in PowerApps. However, if you don’t design your database connections correctly, they can become a major performance bottleneck.
The SQL connector in PowerApps makes creating a connection to a database a synch. Enter the server name and the desired username & password and you are ready to go. However, this ease of use creates a double-edged sword. A novice PowerApps developer may complete a SQL connection and think that he has finished. This is a mistake. By using the SQL connector directly throughout the app, each time it is called it will query the database. This will result in repeated queries to the database when the app is in use. These repeated queries will create a performance bottleneck. Even worse, its effect will not likely show up until the app is shared to many users. Constantly re-querying the database will drag down performance as the number of users scales up. During testing, because only the developer is using the PowerApp, the performance degradation may not be seen. This can lead to frustration and a poor user experience.
Now that you know this is a problem, lets talk about the solution: Collections. Collections are a way to store the data retrieved from a SQL connector. From a high level, what they allow you to do is talk to the database once, retrieve the information you are interested in, and store it in memory. This in-memory collection can then be referenced extensively throughout the app without the need to re-query the database. By using this design pattern, you can create scalable, enterprise-ready PowerApps that are powerful enough to solve your business problems.
Collections in PowerApps
PowerApps implements collections through the Collect() and ClearCollect() functions. You will almost always need a separate collection for each table or view, so you need to know something about the structure of the database you’re accessing to make sure you get all the relevant data you will be displaying or using.
There are, of course, some limitations associated with using collections and SQL:
- Collections are less useful when the data must be frequently updated—think stock and commodities prices, sports scores, etc. In these cases, the downloaded data can get stale quickly. It is pointless to work with stale information so updating from the source will be required for these situations.
- In PowerApps, SQL results are limited to 500 records. You can increase this limit to 2000 records by using prototype features but doing so can hurt the initial download performance. Use delegation functions to filter data before it enters the PowerApp (https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview)
- If the data changes in the PowerApp due to user actions, you must track that change in the collection and eventually you may need to update the database with a Patch() function.
- During the initial data load, the app will appear to be doing nothing, which makes many users a bit anxious. You should implement a “Loading…” animation while the data is loading, so the users know the app hasn’t flaked out on them. Note that this is true whether working directly with a SQL connector or via collections.
Love Your Collections
We’ve discussed previously in this space the fact that PowerApps makes it tricky to update remote databases. Collections do not solve this problem; they are a performance enhancing tool. You will still need some understanding of database design and structure to perform inserts and updates to relational tables within SQL databases.
As a final note, collections can be used for more than just holding SQL data. They are a great way to logically group related items. I often use collections to hold color schemes for branded PowerApps, for example. I assign each control to its appropriate item in the collection and then if I need to change the color scheme, I make the change once and it permeates the entire PowerApp.
Collections are an excellent way to improve performance with PowerApps. Use them extensively when connecting to databases and try them out for other reasons as you get comfortable with them.