PowerApps: improving lookup performance

Here is the scenario: you have two tables in Azure SQL DB or two custom lists in SharePoint that are related to each other via a foreign key (i.e. lookup) and you want to show all columns from the parent table.  You manage to do this, but performance is very slow, the labels in your gallery take seconds if not minutes to show the correct value.

The way to improve performance  is to cache the parent table and to use the cached table for the lookup in PowerApps.  This actually works even if the two tables are not formally related, and even if they are in different systems (i.e. one table in Azure SQL DB and the other in SharePoint).  All you need is a foreign key in the child table which contains the ID of the parent table.

Let me give an example using these tables:

Let us first do things the straight-forward way:

1. Create a gallery and set its Items property to:
   AddColumns(Orders, "CustomerName", Lookup(Customers, Customers[@CustomerID]=Orders[@CustomerID], LastName&", "&FirstName))
   Note the disambiguation needed since the column names are the same in both tables
2. Place a label in the gallery template and set its Text property to:
   OrderID
3. Place another label in the gallery template and set its Text property to:
   CustomerName

You should now see a list of orders, and after a short or long delay see the customer name appearing.  Try to scroll down the orders to test performance.  If performance is slow, then here is how you can modify the app to cache the Customers table in a local collection:

1. Set the OnStart property of the home screen to this:
   ClearCollect(CachedCustomers, Customers)
2. Save/close and re-open the app in order for the OnStart actions to be performed
3. Substitute CachedCustomers instead of Customers in the Items property of the gallery

Performance should now be instanteneous.  Of course the downside is that you would need to re-cache the Customers table every time its data is changed.  This may not be a solution in some scenarios.

If you have more than 500 customers in the Customers table then the above will only cache the first 500 customers.  I will create another post to deal with this.