PowerApps: Caching lookup tables to improve performance

Lookup tables can slow down app performance significantly.  This is because the lookup is performed by going back to the server every time the data is needed.

The way to improve performance is to cache the lookup tables locally once, and then refer to these lookup tables elsewhere in the app.  This works well when the lookup table is unlikely to change during a user session.

I have already covered this topic partially in this post on lookup performance, as well as this post on the User() function; but let me go into some more detail here.

Consider the scenario in which you are managing a stock of products (where each product has a category and a colour) and their quantity in stock.

Your sources are three on-line tables: Products, Categories and Colours. The table Products has an integer lookup column to Categories and to Colours. I am storing these tables in Azure SQL Database, but what I am saying will be the case for any type of table data source.

These tables look something like this:

The products table has 676 rows (AA to ZZ)

You want to create a simple app that will have the following functionality:

  • filter some products by selecting a category with a drop down
  • display the filtered product names in a gallery
  • display the name of the category for each product in the gallery
  • display the name of the colour for each product in the gallery
  • display the quantity (i.e. number of items) for each product
  • display the total quantity of all filtered products in a label outside the gallery
  • display the % of the total quantity for each product in the gallery

The finished single-screen app should look something like this:

Depending on how you design your app, the performance will be dramatically different. Let us go through step by step and this will become clear.

Start with a blank app, connect the three tables as data sources and insert the drop down with which we will select the category. Let’s initially simply set its Items property to:

'[dbo].[Categories]'

(The [dbo]. prefix to table names is only because the tables are in Azure SQL Database, so if you are storing tables elsewhere you probably will not need this)

So far so good, so let’s create the gallery to show the selected products and set its Items property to:

Filter('[dbo].[Products]',
	CategoryId = DropdownCategory.Selected.CategoryId
	)

Insert a label in the gallery’s template to show the product name and set its Text property to:

ThisItem.ProductName

You can now run the app and change the selected category; the gallery should update the list of products fairly rapidly. So far no performance issues, but now it begins to become interesting.

Insert another label in the gallery’s template to show the category name and set its Text property to:

LookUp('[dbo].[Categories]',
	CategoryId = ThisItem.CategoryId,
	CategoryName
	)

This is not really giving additional information since all products should be of the chosen category. This is true, but if you now run the app and change the selected category you may notice a lag between the updating of the products and the updating of the category name in the gallery. This is because PowerApps first updates the items in the gallery and then has to look up the category name from the CategoryId for the gallery label. Fortunately it seems to look up the name only once, and not for each row. But this is because the value is the same for each row. So let’s see what happens with the colours.

Go ahead and create another label in the gallery template and set its Text property to:

LookUp('[dbo].[Colours]',
	ColourId = ThisItem.ColourId,
	ColourName
	)

The lag before the colour names are updated should be longer, and you should also notice that the colours get updated one colour at a time. This means that for each different colour that appears in the gallery, PowerApps is doing a call to the database. Since we have seven colours, this could be up to seven separate calls. If this was customers, where the number of possibilities is larger, the lag could be very long indeed.

So how can we improve the speed?

In this case, since we do not expect the list of categories or of colours to change during the session in which the user is working with the app, then we can simply cache these two tables in collections, for example by using the OnStart property of the screen. This means that the tables will be in memory and no calls to the database will be needed. Even with the additional step of loading the tables into collections, the app should open more quickly and will be much more reactive.

So go ahead now and set the OnStart property of the screen to:

ClearCollect(CachedCategories, '[dbo].[Categories]');
ClearCollect(CachedColours, '[dbo].[Colours]')

This will load into a local collection the categories table and the colours table. Local collections are stored in memory and so can be used in expressions virtually instantaneously and without worries of delegation.

In order for this to work you need to save and close the app and then reopen it, this is because the OnStart actions only happen once when the app is opened.

Now go ahead and change the drop down’s Items property to:

CachedCategories

Then change the Text property of the category name label in the gallery to:

LookUp(CachedCategories,
	CategoryId = ThisItem.CategoryId,
	CategoryName
	)

Similarly for the Text property of the colour name label in the gallery:

LookUp(CachedColours,
	ColourId = ThisItem.ColourId,
	ColourName
	)

You should see that your app will open and react much more quickly than before.

THIS POST IS TO BE COMPLETED

I hope this post has been interesting and useful.  Any comments or suggestions are very welcome.

4 Replies to “PowerApps: Caching lookup tables to improve performance”

  1. Hi Daniel,
    Your blog is amazing. The best place for Azure DB – PowerApps. I wonder if you have any guidance on the best way to setup a new Azure DB that we plan to use with PowerApps? Perhaps simple mistakes or data types to avoid, etc?
    Also, do you know a great place for learning how to compose formulas?

    1. Hi Daniel, you are welcome. I just generated the sample tables with random numbers, so I have not made them available.

Leave a Reply

Your email address will not be published. Required fields are marked *