PowerApps Delegation & Database Design

When I write a SQL Query, I never have to worry if the way I’m writing a query will automatically create a limit on the number of records being returned if I’m not specifying TOP or OFFSET…FETCH. If my query wants to return 1 million records, it will return 1 million records.

PowerApps doesn’t work that way. It uses something called delegation to figure out if the data source can do the filter work required. Otherwise it puts a limit on the number of rows returned. By default, the value is 500 but you can change the limit to 2000, as seen below.

The Data row limit value on the General page is now set to 2000 in my PowerApps setting.

The goal then is to write the logic to pull back that data set using delegation so I’m not hitting that limit. This is where things get tricky. We now have to understand which functions are “delegable” to SQL Server. (And those may differ between the different data sources.) The question becomes what happens if your query is more complicated than these limits allow?

Normally, I would just write a stored procedure to handle this sort of thing. After all, this is what we tell developers to do when their ORM data layers create those horrible queries that we spend months and months tuning. But sadly, that’s not an option here.

This means I have to design a little different from the database side. One of the things I did was create views to bring the different pieces of data together so when I need to use it create an update, the view has pulled all of the data that I need. I may have the view include a single lookup column which is a combination of all of the different columns that I may want to do the search on. This sort of thing would normally make me cringe but depending on what I need to do in my app, it gets me around the limitations of delegation. I’m able to get the information that I can then use in the different ways that I may need to view or pass data to a different piece of functionality. But it also means I need to make sure to design the view properly for the way that we will be searching against it. While it moves around the issue in PowerApps, I still need to make sure I’m not creating a problem for the database.

I’m trying to keep what I’m doing to something fairly simple and manageable. But it does require a different approach as to how I’m retrieving and updating data in my database. This goes to my theory that designing for the app doesn’t mean that we can’t design the database properly to work with it. The database is still just as important to the app for performance as the actual app is.

Now that I can retrieve my data the way I want, time to figure out how to deploy my app…