Users have reported that during work hours a SharePoint document library view takes two and four minutes to load. The library itself holds only 510 documents, and Chrome Developer Tools shows the issue isn't bandwidth related. Once the server returns with the ASPX page, its payload is minimal.
This post attempts to answer what the two to four minutes load time is spent on by breaking down the load time into its constituent components. The test cases consist of sampling five consecutive page loads, changing one variable at a time.
The summary section at the bottom presents a table of measurements.
In order to setup a baseline on which to improve, we sample the slow view (at Documents/Forms/AllItems.aspx) and use the average load time and its standard deviation. On shared infrastructure, collecting decent samples may require a few retries. In all measurements, we sample three times and pick the best result. We should also take into account how SharePoint Online uses the object cache. Frontend servers have object caching enabled, but compared to on-premises it has mostly no effect on load times.
For the baseline, we arrive at 27.9 seconds of average load time.
To generate candidate test cases for improving page load time, it's worth reviewing typical causes of slowness:
Do the document library and its 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.
What's the navigational complexity? SharePoint defaults to structural navigation for the top links and quick launch menus. Navigation options include structural navigation, managed navigation, and search-driven navigation. In this case, the navigation is structural with the web inheriting top links from its parent, consisting of multiple levels of web links.
How many lookup fields does the view contain? Lookups are fields of the type Managed metadata or Person or Group. These may cause SharePoint to generate additional queries. Of the list's 11 lookup fields 10 are part of the view.
Does the view have a paged 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 view filtering to locate a document. For the view in question, the item limit is 300.
Based on these typical causes of slow performance, we want to start by looking into the cost of rendering the non-view parts of the page. In addition, the cost of an item limit of 300 needs investigating. Lastly, the two-level group by on Managed Metadata fields, including group expansion, can prove potentially costly.
We can measure the overhead of non-view components on the page by sampling against a page which cannot be modified and which has minimal dynamic content. One such page is the Site Settings application page (at _layouts/15/settings.aspx). While its content is static it includes both top links and quick launch menus. To gauge its relative performance, we measure against the Site Settings page of an out of the box team site.
On average the Site Settings page takes 3.5 seconds to load. The same page on an out of the box team site, however, takes only 0.7 seconds to load. Hence 2.8 seconds is spent loading the navigation components. In other words, short of changing the navigation, 2.8 seconds is the lower bound on the view page load time (and any other page on that web).
We didn't expect group by on Managed metadata fields to have a significant impact on the load time. Group by on so few items would like seem like a computationally fast operation. Trough flattening the view, keeping view settings constant while disabling group by with expansion, yields a surprising result. The Load time goes down from 27.9 seconds to 7.5 seconds. Perhaps SQL Server isn't optimized for group by operations in views.
Factoring in the 2.8 seconds non-view load time, 7.5 seconds is bordering acceptable. Users may have a good business reason for group by, in which case adding refiners to the view 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. Contrast those numbers to the 4.7 seconds it takes to load a view with only the name field and the 4.8 seconds it takes to load the view with group by but without expansion.
By reducing the item limit from 300 to 30, even including group by and expansion, page rendering time can be reduced to 6.4 seconds. If we disable group by we arrive at 4.2 seconds.
The table below summarizes all measurements conducted outside business hours from an Azure virtual machine:
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
We've managed to bring down load time from 27.9 seconds to 4.8 seconds by disabling expansion and 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 these numbers are within SharePoint's normal operating parameters.
In addition to the tweaks above, for large lists and libraries, setting up metadata navigation might be another avenue worth pursuing. Even when metadata navigation hasn't been explicitly setup for a list or library, the feature contains a query optimizer which works to improve view performance by automatically creating indices based on use.