Optimize "Get items" performance

[CharlesPP]

Hello everyone,

I'm experiencing performance issues with the "Get items" action to retrieve items from my SharePoint list. Currently, the execution of this action can take more than 30 seconds, which is too long for my use case.

Here's what I've already implemented to optimize performance:

• Filtering: I filter only on indexed fields
• Columns: I retrieve only the necessary columns (thanks to "Limit Columns by View")
• Maximum number of elements: I have limited the number of elements retrieved to 500 (thanks to "Top count")
• Pagination: I have activated pagination, also with a limit of 500 elements

Here are a few more details that might be useful:

• SharePoint Online list: The list I'm working with is quite large (over 30,000 items)
• Context: The "Get items" action is crucial to my workflow and needs to be as fast as possible, as it returns data to the user of a Power Apps application

I'm looking for additional tips or techniques to improve the performance of this action. Are there any settings or practices I may have missed? Specific configurations for large SharePoint lists?

Any help or suggestions would be greatly appreciated. Thank you in advance for your time and expertise.

Best regards,

Charles

[wskinnermctc]

Are you able to filter the list directly with the power app?

Reza Dorrani - Power Apps Search and Filter Functions on Large SharePoint List

[CharlesPP]

Thanks @wskinnermctc for the feedback. I had already seen this video and reviewed it to remind myself, but unfortunately it doesn't solve my problem, as almost all my filters are non-delegable (are multiple combo boxes) and non-mandatory (therefore don't bring back less than 2000 items). Currently, the filters entered are sent to my Flow Power Automate, which converts them into a filter query used in Get items.

[royg]

Haven't tested, but perhaps you can try using the Send HTTP action to get the filtered items and fields you need. Set the Auth header to 

Accept: application/json; odata=nometadata

 Hope this helps

[CharlesPP]

Thanks for the suggestion!

I had tried this, but without adding the header.

I'm having trouble getting anything working with this technique, because when it does work, it goes very fast, but according to the filter query I get the error "BadGateway" I have no idea why, I haven't been able to identify where the error is coming from.
Here's an example of a query that gives "BadGateway":

_api/Web/Lists/GetByTitle('MyList')/Items?$select=ID,Origine,Historise,MyLookup/Title,[.....]$expand=MyLookup,[.....]$filter=(Origine eq 'Réglementaire') and Historise eq 0&$top=2000

But when I run the filter query with certain values other than "Réglementaire", it works... This is an example, but most filter queries don't work, even though they work with Get items. I tried using the uriComponent() formula on the filter query, but it didn't solve the problem.

Thank you very much for your help.

[royg]

Interesting, I added that value to a text field in a list and was able to filter by it. perhaps it's a combination of the expand. can you exclude it (you can add a different query for the lookup list and join the values by the id later)

[CharlesPP]

This is really strange. I ran some tests simplifying the query and here's the result. "Origine" is a choice field with the values listed in the following tests :

Does not work :

_api/Web/Lists/GetByTitle('Echeances')/Items?$select=Origine,Historise&$filter=(Origine eq 'Réglementaire') and Historise eq 0&$top=2000
_api/Web/Lists/GetByTitle('Echeances')/Items?$select=Origine,Historise&$filter=(Origine eq 'Réexamen périodique') and Historise eq 0&$top=2000

Works :

_api/Web/Lists/GetByTitle('Echeances')/Items?$select=Origine,Historise&$filter=(Origine eq 'Conformité') and Historise eq 0&$top=2000
_api/Web/Lists/GetByTitle('Echeances')/Items?$select=Origine,Historise&$filter=(Origine eq 'Inter-Réexamen') and Historise eq 0&$top=2000
_api/Web/Lists/GetByTitle('Echeances')/Items?$select=Origine,Historise&$filter=(Origine eq 'Autres') and Historise eq 0&$top=2000
_api/Web/Lists/GetByTitle('Echeances')/Items?$select=Origine,Historise&$filter=(Origine eq 'Courrier') and Historise eq 0&$top=2000
_api/Web/Lists/GetByTitle('Echeances')/Items?$select=Origine,Historise&$filter=(Origine eq 'DAPE') and Historise eq 0&$top=2000

 Thanks in advance!

[royg]

Wow, if you query all items, no filtering, do you see Réglementaire or Réexamen périodique  in the returned data?

[CharlesPP]

Yes, I find "Réglementaire" and "Réexamen périodique" in the "Origine" choice field when I don't set any filter.

I've given the example of the "Origine" filter, but I've seen similar things in other fields.
For example : 

• The filter on the "Reference" text field always works : _api/Web/Lists/GetByTitle('Echeances')/Items?$select=Reference,Historise&$filter=(Reference eq 'MyReference') and Historise eq 0
• The filter on the "MainAffaireTome" lookup field never works (BadGateway) : _api/Web/Lists/GetByTitle('Echeances')/Items?$select=MainAffaireTome/Title,Historise&$expand=MainAffaireTome&$filter=(MainAffaireTome/Title eq 'Lookup value') and Historise eq 0
• The filter on the "Date" date field never works (BadGateway) : _api/Web/Lists/GetByTitle('Echeances')/Items?$select=Date,Historise&$filter=Historise eq 0 and Date ge '2040-01-01'

If you have any ideas, I'd be glad to hear them. These filter queries all work with Get items.

[CharlesPP]

After a few tests, I'm wondering if this isn't related (in part) to the volume of data returned, here are some other examples that make me think this:

Does not work:

_api/Web/Lists/GetByTitle('Echeances')/Items?$filter=(Origine eq 'Réglementaire') and (Date ge '2040-01-01')
_api/Web/Lists/GetByTitle('Echeances')/Items?$filter=(Date ge '2010-01-01')

Works:

 _api/Web/Lists/GetByTitle('Echeances')/Items?$filter=(Date ge '2040-01-01')

What's surprising is that filtering on the date after 2040 AND having a filter on the origin doesn't work, while filtering only on the date (2040) does.

Thanks a lot!

[CharlesPP]

Hello, I would like to re-launch this topic. Do you have any ideas for improving the performance of "Get items" in my context, or for solving my bug when using "Send an HTTP request to SharePoint"? Many thanks in advance !

[royg]

Can you try indexing the specified columns?

[CharlesPP]

Thanks for your answer! I already filter only on indexed columns.

[takolota · Accepted solution]

@CharlesPP 

I have done some work optimizing a flow using the SharePoint HTTP action to return items to a Power App and I have gotten it to return up to 20,000 items to the app in under 10 seconds. And it will work for any combination of filtering and/or searching the list.

https://powerusers.microsoft.com/t5/Community-App-Samples/No-Delegation-Limit-SharePoint-List-Power-...

Video explaining more of the flow: https://youtu.be/V1d3V_cWVaA?si=C1t1AsxkK5Aruqvd 

This method of only doing batches of up to 5000 at a time in a concurrent Apply to each should also get around issues you previously mentioned where you couldn't apply multiple filters at once. Because it does allow more filters to be applied when your initial piece of the query, like querying on the ID column, restricts the initial outputs to less than the 5000 list view threshold. 

_____________________________________________________________________________

Subscribe to my YouTube channel

[CharlesPP]

Thanks for your reply! It works correctly and quickly (less than 5s), I've adapted your flow to my use case and simplified the process. However, it crashes when the filter query is too long, which doesn't happen with Get items. Do you have any idea how to fix this?

Also, would you have any idea why my previous attempts failed (see previous posts).
Thanks a lot!

[takolota]

@CharlesPP 

I’ve used 3-4 filters at once with this set-up, what is the error you are getting?

And what specific previous issues? If you mean applying multiple filters then it is because SP is limited to what filters it can use on different columns if it is trying to apply said filter to more than 5000 rows.