Power Automate Get Items Action: Pagination and Top Count settings with large SharePoint lists

[westerdaled]

Just had client get in contact as one of their main flows has stopped working when performing a lookup  single related list with 5000+ items:

Get Items - Lookup the related item in a child list.

RelatedId = IdInAnotherList

If you look at the Known Limitation, which is as clear as mud, but doesn't actually state what the Pagination limit should be, per list size .  So is there any recommendation, I should flow for the running Get Items against list sizes as shown in the table below , so I can pre-empt these Get Items Actions failing. Note, all the flows are running under an account with a  Microsoft 365 E3 license .  

I put together the table below to prompt a discussion.  The first row I tried today allowed me to get all the failed flow runs to restart and keep the client happy.

List Size	Pagination Limit	Top Count limit	Comments
5000	5000	5000	Flow started working, Yay but are these settings recommended.
10000	?	?	Plan 2?
15000	?	?	Plan 2?
20000	?	?	Plan 2?

[srduval · Accepted solution]

I always max out the pagination limit, future proofs my work.

[Tono_Analyst2]

I am also confused on this configuration - did you ever find the answer @westerdaled. 

@srduval whilst this 'solves' the problem in most instances, simply setting it to the max doesn't really explain the problem nor gives piece of mind that this will be future proof. When will this stop working etc. 

[westerdaled · Accepted solution]

@Tono_Analyst2 

I agree with @srduval  . To explain, The Get Items action by default returns 100 items per call

So if if you want more items per call then change the pagination threshold to say 5000 ( max).  I may also changed the number of items returned to 5000, even though I just wanted to lookup item. I refactored the flows in question,  over a year ago , so my memory is little hazy🙂.

  Remember to check your list indexes as these are helpt to avoid List View Threshold issues in your Power App.

I suggest you set up some test cases against your large list  and test out out your flow , then report back the results. 

[srduval]

@westerdaled explained it well. It's a design feature/limitation on the API that is calling the sharepoint site. Most of microsofts APIs have a limit to the amount of data that can be reported back in one go, some of the actions in power automate handle the looping better than others, but if you look at the raw code, alot of them have continuation tokens that have to process in order to get page two.

The limitation is in how many pages it can store in memory. Basically you're copying a bunch of information, but you have to know before you start how much memory (large/storage) it will take to download it all, otherwise it will just download what fits. 

[Tono_Analyst2]

Thank you @westerdaled and @srduval  for your explanation. 

I understand that the view threshold limit of SharePoint is 5000. However, in the Get Items pagination threshold input, it says that the max is 100000.

When you say you always max out, do you set it to 5000 or 100000? Is setting it above 5000 pointless?

@westerdaled as you suggested, I will build out a large test list of +100,000 rows and test it out 🙂 
If I've understood correctly, you suggest a combination of creating an indexed column on the filtering column(s) and maxing out the pagination limit in Get Items.

[srduval]

I have a premium license, so I usually do 100000. without that license you're capped at the 5k level. The view threshold limit and how much data can be stored in a list are two different things, I've loaded a list up exceeding that threshold and was still able to query data from it despite not being able to open the list in sharepoint. So setting above 5k is not pointless, but 99.9% of the time it will be way more than you need. Same with 5k it probably wouldn't be a very common occurrence to exceed that level

[westerdaled]

@Tono_Analyst2 

I think the consensus is to try the pagination limit of 100000 with say a Top Count of 5000 in your tests.  In my flow I had captured the ID of the related item, so used this in my filter query. Note, IDs are auto indexed by default . If you are using another column to search on, then yes I suggest you check whether the index exists or not.  Please report back the results on your tests.   

[Tono_Analyst2]

@westerdaled @srduval I have created a list of ~200,000 rows and ran multiple tests please refer to below:

Context

For testing purposes, a SharePoint list consisting of 200,285 rows was created. Refer to the table schema below:

ID	Color	Animal
1	Grey
2	Red
3	Red
4	Green
…	…	…
200284	Blue	Zebra
200285	Blue	Horse

• Note 1: Animals are blank until the 30000th record. This is intentional to test the query behaviour when data is found outside of the SharePoint List view threshold limit (5000 rows).
• Note 2: The color Grey has been scattered across the SharePoint strategically at the following rows:

ID	Color	Animal
1	Grey	(irrelevant)
5000	Grey	(irrelevant)
5001	Grey	(irrelevant)
6001	Grey	(irrelevant)
20000	Grey	(irrelevant)
200000	Grey	(irrelevant)

Test 1: No indexing and pagination deactivated

Filter Query	Order By	Top Count	Output Count	Summary
Animal eq 'Dog'	ID asc	Blank	0	No rows returned. Without indexing or pagination it will only query the first 5000 (accounting for ordering)
Animal eq 'Dog'	ID desc	Blank	100	After sorting the list in descending order (where animals exist at the end of the list), the query looks at the first 5000 and returns up to 100 without pagination enabled.
Color eq ‘Grey’	ID asc	Blank	2	Without indexing or pagination it will only query the first 5000 (accounting for ordering)

Test 2: No indexing and pagination activated (100)

Filter Query	Order By	Top Count	Output Count	Summary
Animal eq 'Dog'	ID asc	Blank	100	Rows returned based on pagination limit (<1 s)
Animal eq 'Dog'	ID desc	Blank	100	Rows returned based on pagination limit (<1 s)
Color eq ‘Grey’	ID asc	Blank	6	All rows returned (~1min). Takes longer to retrieve for some reason?

Test 3: No indexing and pagination activated (10000)

Filter Query	Order By	Top Count	Output Count	Summary
Animal eq 'Dog'	ID asc	Blank	10000	Rows returned based on pagination limit ( ~ 2 min)
Animal eq 'Dog'	ID desc	Blank	10000	Rows returned based on pagination limit (<1 s)
Color eq ‘Grey’	ID asc	Blank	6	All rows returned (~1min). Takes longer to retrieve for some reason?

Test 4: Indexed columns and pagination deactivated

Filter Query	Order By	Top Count	Output Count	Summary
Animal eq 'Dog'	ID asc	Blank	0	No rows returned. Possibly a bug? Why are there no rows returned, there should be at least 100 without the pagination.
Animal eq 'Dog'	ID desc	Blank	100	After sorting the list in descending order (where animals exist at the end of the list), the query looks at the first 5000 and returns up to 100 without pagination enabled.
Color eq ‘Grey’	ID asc	Blank	6	IDs [1; 5000; 5001; 6001; 20000; 200000] returned. When indexing is applied it will query all the data and return up to 100 if pagination is deactivated.

Test 5: Indexed columns and pagination activated (100)

Filter Query	Order By	Top Count	Output Count	Summary
Animal eq 'Dog'	ID asc	Blank	100	This time 100 rows are returned as expected.
Animal eq 'Dog'	ID desc	Blank	100	100 rows returned as expected
Color eq ‘Grey’	ID asc	Blank	6	All 6 rows returned as expected

 

Test 6: Indexed columns and pagination activated (5000)

Filter Query	Order By	Top Count	Output Count	Summary
Animal eq 'Dog'	ID asc	Blank	5000	5000 rows returned. Query takes longer (~1 min)
Animal eq 'Dog'	ID asc	5	5000	Error: The action 'Get_items' has an aggregated page count of more than '1000'. This exceeded the maximum page count '1000' allowed.
Animal eq 'Dog'	ID desc	Blank	5000	5000 rows returned. Query takes longer (~1 min)
Color eq ‘Grey’	ID asc	Blank	6	All 6 rows returned (no time penalty observed)

Test 7: Indexed columns and pagination activated (10000)

Filter Query	Order By	Top Count	Output Count	Summary
Animal eq 'Dog'	ID asc	Blank	10000	10000 rows returned as expected (~2mins)
Animal eq 'Dog'	ID desc	Blank	10000	10000 rows returned as expected (~2mins)

Summary:

• Pagination must be enabled to retrieve all data. The threshold limit that can be assigned as part of the pagination setting will determine the maximum number of rows the Get Items action will return.
• The indexing in this test didn’t seem to make much of a difference in terms of performance. However, another SharePoint did require indexing to sort/filter a dataset of ~12000 which is much less than this test list.
• Possible bug observed with the configuration ‘Indexed columns, but no pagination”. It was observed that when filtering for Dog it returned no rows at all. My first assumption was that without pagination enabled, it cannot retrieve more than the first 5000 records (after sorting). However, filtering for the color Grey did return rows beyond 5000. Strange behaviour! I would expect that it should still be able to return 100 records.

Let me know your thoughts