Issue: business users complain that the page loading of a SharePoint listview takes a long time, up to a minute. During page loading + rendering, the browser is totally unresponsive (Chrome even pops up a dialog about page unresponsive).
In the analysis for the root cause of this structural slow performance I observe that the list contains multiple Lookup fields to other lists in the site, and also a 'Person or Group' field - which is a specific type of Lookup, to the hidden UserInformationList.
This multitude of Lookups in the list is the most significant cause for the slow performance. However, also on user experience / functional level, it is wrong designed: the usage of the page is to first load + display all the items from the list, and next the end-user must filter to select the relevant items.
I therefore first aligned with business owner on another functional approach: let the end-user start with specifying the relevant filter(s) [can be a combination of filters for multiple columns], and then select + retrieve only the items from the list that satisfy the filter-conditions. For the technical design, the Search tool must be setup future-proof, aka cloud ready. Thus interoperating with SharePoint either via CSOM or via REST services. I decided to utilize REST, so that data is returned in JSON data-format, and can be directly data-binded in client-side UI (I used Knockout.js; but same holds for other clientside UI frameworks as Angular, Ember).
In SharePoint REST one uses the $expand parameter to include referred lookup values in the result set. But similar as for XsltListView, this quickly destroys any performance:
So I needed a way to avoid the Lookup-expands, while still being able to filter on and select the values of the Lookup fields. Totally getting rid of the Lookup columns is not an option: for consistent data-management it is a Must-Have that one can select only from the values maintained in the Lookup list. And it holds even more for the Person field: selecting via the PeoplePicker ensures a user-friendly selection + validation.
The approach I decided to is to 'flatten' the lookup values in additional columns. Functional management can still manage the data-items using the lookup functionality, and on data-selection + retrieval I avoid the need to $expand.
The elements of the approach
- Per lookup value that is needed in the selection and/or display, add a single-line of text column to the list. Set them to hidden in the Item content type, so that their existense is invisible for functional management in the New/Edit/View list-forms;
- For the 'Person or Group' column, include 4 new fields, for
- Person Name,
- Photo,
- Department,
- and the UserId in the UserInformationList
- Create a SharePoint Designer Workflow on the list that activates on ItemCreated + ItemModified events. Design the workflow to propagate ('flatten') the lookup values to their respective flattened counter-field;
- Realize the bulk flattening of the existing list items through the same workflow, via javascript start the workflow on every item.
Impression of the solution setup + result
