cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NewDev
Frequent Visitor

Large SP list with users able to read only own items : when filtering on user in powerapp performance collapses, when no powerapp filter performance is perfect - why and how to avoid it?

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

ClearCollect(
    UserSessions,
    Filter(
        Signups,
        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

ClearCollect(
    UserSessions,

    Signups
);

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.

4 REPLIES 4
NewDev
Frequent Visitor

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?

NewDev
Frequent Visitor

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.  

Hi @NewDev  

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 

 

Set(CurrentUser, User())

 

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/ 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,602)