Recently we have used Content Query Web Parts in one of the SharePoint Online Intranet environments and one of the first things we have noticed was severe performance issues while loading department and corporate landing pages hosting CQWPs. Before I proceed, you may ask why would I use CQWP when search based technologies available but there are always reasons why you would use one technology over another (read delay in content refresh due to search crawl).
In this article, I wanted to highlight some of the methods and approaches we had explored to improve CQWP and what worked and what’s not.
Limit the Content Source
CQWP is known to rollup data with in same site collection based on either list type or content type. Options available are – Show items from all sites in this site collection, Show items from the following site and all sub sites, and Show items from the following list. Larger the content source and number of sub sites, content query web part can take longer to query data from all the sub sites and lists. Smaller the content source for specific list, faster the performance. As you increase the content source to sub sites or all the sites in site collection, it will affect the overall performance.
You may ask, why use CQWP in SharePoint Online to display data from specific list in specific site when many better options available. One of the major reasons are if you can’t use App Model, Search based web parts, there aren’t many options to display list data in specific design format apart from Content Query web part in no-code cloud solution model. Without support for coded Sandbox solutions (was supported in SharePoint 2010 online), you may able to use Script editor web part which would allow you to use CSOM or REST based APIs to query list data but CQWPs are easy to design with custom XSLT which might provide better maintenance story & reusable templates for future updates.
New feature SharePoint 2010 CQWP – SLOTs makes it even easier to design and maintain CQWPs as no-code solutions. If you are planning to use CQWPs to display data from specific list or sub sites, plan to limit the content source as much as possible to improve the performance. In our case, our CQWPs are limited to specific SharePoint list which wasn’t an option to improve performance further.
View field override
By default, CQWP returns all the fields from the SharePoint list but you can specify ViewFieldsOverride to return only sub set of fields you would need to display, sort, and filter. In theory, this should improve performance due to less overhead on backend query engine but we didn’t see any noticeable performance improvement.
Create Index for filters
If you are querying SharePoint List whether it’s custom code or CQWP web part, this is no brainier. Creating index for columns used for sorting and filtering on the SharePoint list should improve performance but this is another approach we tried without great performance improvement.
As many of us are aware, SharePoint uses Object Cache to improve performance for CQQP and cross-list queries in addition to caching sites, lists, libraries, page layouts and pages objects on the web front-end servers RAM.
I have used Object Cache in past successfully to improve CQWP and cross-list queries performance on on-premises farm. Just like many other server and hardware level features, SharePoint Online doesn’t support Object Cache and SharePoint Administrators have no control over enabling or increasing size of Object Cache in SharePoint Online. This was a big disappointment news for us. If Object Cache would have been available (we understand the reason why it’s not enabled on multi-tenant shared hardware), it would be great boost for CQWP performance improvement. If you are reading this article to improve performance in On-Premises environment, read no further and use this approach.
With publishing features enabled (required for CQWP), you have one more option to improve the page performance by using Output caching option. For each output cached version of page request, the server does not have to make a round trip to the database to fetch the source code for the .aspx page, any .ascx controls on the page, reload and re-render the controls, and re-query any data sources that the controls rely on for data. Although technically this shouldn’t improve the CQWP performance, we were lucky enough to have option to enable Output Caching on SharePoint Online tenant. You can enable output caching at site collection, site, or page layout level.
Since we had CQWPs on home page landing pages, after enabling output caching at both site collection and page layout level, we noticed huge performance improvements. Output caching may not be ideal solution for SharePoint On-premises farm to improve CQWP performances, it did the magic in SharePoint Online.
Hope this helps!!!!