I have a very large SP list (over 500K items) and a PowerApp that should display for each user his(her own items. I initially had a filter on the user's email like the following (Signups is my SP list and Attendee is a column of type Person in the list and it is indexed) to filter out user's list items
Attendee.Email = User().Mail
but then seeing the awful performance I tried removing the filter counting only on the filtering done by the SP List itself and indeed the is an abyss between the two, so now the following is superfast
Since I have users with higher privileges on the SP list (they can see all items) I would still need the filter to filter out other user's items.
What I'd like to understand is the reason for the performance difference. It seems like with the filter in place filtering is done on the entire set of items in the list other than only those accessible to the user but I'm missing why this is happening and how to avoid it.
I tried to give myself an explanation to this behavior and it looks like when applying a filter on the List data source the filter gets executed on all items regardless of what the user should be able to read resulting in an awfully long query time.
Could this be possible? Any way to investigate further this issue?
After further tests I got to the conclusion that what is killing the filtering is that the column is of type person. Although the column is indexed filtering on any of its properties (e.g. email) is extremely inefficient.
People is dynamic column. SharePoint team doesn't recommend to use many dynamic columns in the list.
I'd like to suggest you should use text column to keep user name or alias and filter against the static column instead.
Although you tune dynamic fields(people) to static one, if you have many columns and each user has many records, chances are slow performance could be there.
For the best performance, if you would to select data per user, you'd better consider CDS or SQL rather than SharePoint.
Using a View in CDS could perform much faster than the current.
If you share sessionId or AppId, I can check PowerApps telemetry to see what performance bottlenecks presented.
Plus, hope you can get some recommendations.
One of the reasons that your app runs slow is that the User() function has to query the list every time it is referenced in a formula. . The problem is your filter has User.email in it. To solve this problem, cache the current user when the app starts. If you put
in your OnStart property of the app, then use CurrentUser.email in the formula, you should see a better performance. The explanation for how this works and why it works is here https://baizini-it.com/blog/index.php/2017/08/29/powerapps-user-function-cache-current-user-onstart/
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Join us on June 28 for our monthly User Group leader call!
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.