Users have reported that during work hours a SharePoint document library view takes two to four minutes to load. The library itself holds only 510 documents, and Chrome Developer Tools reveals that the issue isn't a bandwidth issue. Once the server returns the ASPX page, containing the view data, the ASPX page size is 169 KB and the total page payload is 236 KB cached and 2.6 MB not cached. Furthermore, because the ASPX page is the one referencing images, JavaScript, CSS, and the like, no other resources begins to load until the ASPX page is returned and parsed.
This post attempts to pinpoint what the two to four minutes load time is spent on. It does so by breaking down the load time into components of the view and page, such as grouping, item limit, managed metadata fields, and navigation. Each test case exercised below consist of a sampling of five page loads, changing a single variable at the time.
On shared infrastructure, it may require a few attempts to gather consistent numbers with a consistent average and low standard deviation. For all test cases, we sample three times and pick the best. We should also take SharePoint Online's use of the object cache into account. Frontend servers do have object caching enabled, but compared to on-premises it has little effect on load times.
The concluding section at the end presents a summary of the timing of each test case.
To establish a baseline on which to improve, we sample loading the slow view (Documents/Forms/AllItems.aspx) and arrive at an average load time of 27.9 seconds.
Starting from typical causes of slowness, and knowing that slowness has likely crept in gradually as the number of list items or webs increased, we can form hypotheses to test:
Is the view's item limit above the default 30? A larger value tends to make little sense as users aren't likely to read through a long list anyway. Instead users apply additional filtering to find a document. For the view in question, the item limit is 300.
How many lookup fields does the view contain? Lookup fields are those of types Managed metadata and Person or Group. These cause SharePoint to run additional queries against the hidden users list and hidden taxonomy list of the root web. When in edit mode the real user and taxonomy store is used, but views rely on the hidden lists for better performance. Of the document library's 11 lookup fields 10 are part of the slow view.
What's the web's navigation complexity? SharePoint defaults to structural navigation for the top links and quick launch menus, but other navigation options such as managed navigation and search-driven navigation exist. In this case, the navigation is structural with the web inheriting top links from its parent, consisting of multiple levels of web links.
Does the document library and items retain role inheritance? Breaking role inheritance means SharePoint must do extra work to security trim elements in the view. In this case, the web, list, and items inherit permissions from the parent.
Based on the typical causes of slow performance, we start by looking into the cost of loading the non-view parts of the page. Moreover, the cost of an item limit of 300 and the cost of two-level group by on Managed Metadata fields, including group expansion, calls for investigation.
We can determine the impact that non-view components have on view load by measuring against a page which cannot be modified and which has minimal dynamic content. One such page is the Site Settings application page (_layouts/15/settings.aspx). While its content is static it includes both top links and quick launch menus. Furthermore, to gauge relative performance, we also measure against the Site Settings page of an out of the box team site without inherited navigation.
On average the Site Settings page takes 3.5 seconds to load. The same Site Settings page on an out of the box team site, however, load in only 0.7 seconds. Hence 2.8 seconds is spent loading the navigation components on the page hosting the slow view. In other words, short of changing the navigation, 2.8 seconds marks the lower bound on view page load time (and any other page load time on that web).
Flattening the view by disabling group by on two managed metadata fields while keeping expansion turned on, we arrive at a surprising result. The load time goes down from 27.9 seconds to 7.5 seconds. That's a surprise, given that the document library holds only 510 items and that group by on so few items would seems computationally fast. Perhaps SQL Server isn't optimized for group by operations in views, perhaps it's the fact that we're grouping on managed metadata fields that throws off the optimizer.
Factoring in the 2.8 seconds non-view load time, 7.5 seconds is bordering acceptable. Users may have a good business reason for grouping, though, in which case setting up metadata navigation on the list might be worth pursuing.
Next, we measure the cost of the view's 10 lookup fields by removing those from the flat view. It causes an additional decrease in load time from 7.5 seconds to 5.1 seconds, meaning the lookup field overhead is 2.4 seconds. Compare those numbers to the 4.7 seconds it takes to load a view with only the name field (meaning that the remaining non-managed metadata fields have almost no overhead) and the 4.8 seconds with the original fields but expansion turned off (meaning that expansion is an expensive operation).
By reducing the item limit from 300 to 30, even including group by and expansion, page load time becomes 6.4 seconds. If we disable group by we arrive at 4.2 seconds.
The table below summarizes the measurements performed outside business hours and from an Azure virtual machine. Using an Azure host eliminates potential issues with the local network skewing the result. The assumption is that it's more likely to experience issues on the local network than within the Azure infrastructure:
Avg StdDev
= Sort by name, two-level group by expanded, 300 item limit 27.9 2.4
With 30 item limit 6.4 0.9
Without two-level group by expanded 7.5 0.7
Without two-level group by expanded, without lookup field 5.1 0.5
Without two-level group by expanded, without all but name field 4.7 0.2
Without expansion 4.8 0.4
Without two-level group by expanded, with 30 item limit 4.2 0.3
Without non-view part of baseline page 3.5 0.4
= Non-view part of out of the box new web 0.7 0.1
All in all, we've managed to bring down view load time from 27.9 seconds to 4.8 seconds by disabling expansion and down to 4.2 seconds by disabling group by and limiting the view to 30 items. Considering the constant non-view overhead of 2.8 seconds, both of these numbers are within SharePoint's normal operating parameters.
In addition to the tweaks above, setting up metadata navigation might be an avenue worth pursuing. Even when metadata navigation hasn't been explicitly configured for the list, enabling its Metadata Navigation and Filtering feature turns on an automatic query optimizer which creates up to 20 indices on the list a based on use.