Optimize "Get items" performance

(0) Share

Report

Posted on by CharlesPP

187

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

Categories:

Building flows

I have the same question (0)

All responses (15)

Answers (1)

wskinnermctc 6,519 Moderator on at

Like (1)

Report

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

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

Was this reply helpful? Yes No
CharlesPP 187 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
royg on at

Like (0)

Report
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

Was this reply helpful? Yes No
CharlesPP 187 on at

Like (0)

Report
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.

Was this reply helpful? Yes No
royg on at

Like (0)

Report

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)

Was this reply helpful? Yes No
CharlesPP 187 on at

Like (0)

Report
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!

Was this reply helpful? Yes No
royg on at

Like (0)

Report

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

Was this reply helpful? Yes No
CharlesPP 187 on at

Like (0)

Report
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.

Was this reply helpful? Yes No
CharlesPP 187 on at

Like (0)

Report
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!

Was this reply helpful? Yes No
CharlesPP 187 on at

Like (0)

Report

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 !

Was this reply helpful? Yes No