Expanding multiple SharePoint people columns is very slow

[jsangerman]

I am working with a SharePoint list that has about 10,000 items. Five of these are personcolumns that I need to get UserID from, and doing so is very slow. In total, my query is taking over an hour to run, and that seems to be almost entirely due to the expansion of these 5 person columns. My organization has tens of thousands of users, so loading that list for every single row and each of those columns takes a long time.

If I was dealing with a non-person lookup columns, I would load the lookup table separately and merge using the lookup column Id.  That would mean the lookup table is only loaded once. With a person column, I have the lookup Id. Is there a way to load the person lookup table?

By the way, I have tried using implementation 2.0 of the connector, but I get an error because the list has more than 12 lookup/person columns. I don't own the list, so I can't "fix" that.

If not loading the person lookup table, is there another way I can improve the performance of this?

[v-heq-msft · Accepted solution]

Hi @jsangerman ,
Based on your description, first of all on how to load the user information list separately, I checked some documentation on how to access the SharePoint user information list by navigating to “/_catalogs/users/simple.aspx” from your site via your browser. This assumes you are a SharePoint administrator.
SharePoint User Information List (Hidden List) - Enjoy SharePoint
Use the REST API to get the data in the user information list. The example is as follows:
GET https://<your-sharepoint-site>/_api/web/siteusers
This will return information about all users, including user IDs, logins, emails, and more.
If you don't want to load a list of user information, you can try building a correlation table for lookups and links based on the “_attributename_value” field as mentioned in the following post, instead of “expanding” the attributes in the record.

Solved: Refresh very slow when using SharePoint people pic... - Microsoft Fabric Community

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jsangerman]

That is exactly what I was looking for. Unfortunately, I'm not a SharePoint admin, so I can't access it.

I guess there's no other way to do this but to expand those records, which is unfortunate.