Power Apps with large Sharepoint lists – performance with indices

Getting Power Apps to work with large Sharepoint lists can be tricky – but its doable! When you pay attention to indices you can even keep up a relatively good performance!

List view threshold seems to play a large role here. Currently for the Sharepoint Online it is 5000 items. That is the maximum number of items that you can manage or view through normal Sharepoint UI. Maximum number of items is totally a different thing. Currently you can have up to 30 million items in a list!

Since I’m writing about using Sharepoint lists as part of your Power Apps solution, we are not that interested about what Sharepoint UI can or cannot handle. Power Apps will access your lists trough API and that’s the case here.

The most important thing with using large Sharepoint lists is to remember that 5000 items limit. What ever you do, you should always think twice if that limitation is affecting you. If you’re complete new to this, I would recommend starting with reading the documentation about delegation in a canvas app. Using delegable functions you can access your items from a list as far as you won’t need more than 5000 items at a time.

I will concentrate here to using filter function. My remarks apply to both access from canvas app directly or from Flow. I had to think this for quite some while, so hopefully this helps you to get it a bit easier:

  • First argument should contain a column that has been indexed
  • Filter cannot return more than 5000 items when all arguments are combined, so I guess for most cases you want to design the query accordingly. You can have first argument that would return more items and then filter more with other arguments. This will however develop performance issue, and that is why most of the times:
  • You want to make sure that the first argument limits items to less than 5000. This is a must to keep good performance!
When your first filter argument selects more than 5000 items, paging will happen at the first stage of the process. Every page will result separate response to your app, even if second argument drops all the items from selection!

Below you can find some examples which I observed investigating this:

Flow “Get Items” action returns only “@odata.nextLink”, when you expected to get some items. In this case your list contains more than 5000 items. You’re trying to fetch item that is found later than in the first 5000 items found by the first argument. Flow supposedly takes “nextLink” as that “top 1” and you end up not receiving item that never the less is on your list!

If filter argument 1 matches for more than 5000 items, you may end up with an empty response

Power Apps monitor showing multiple pages of responses with “received 0 rows”. For my case this meant approximately 20 seconds added delay for the query, although the result remains the same.

Power Apps monitor showing delegated filter function to result as multiple pages of “received 0 rows”.
This is what well performing query is supposed too look in the monitor.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.