My first car, a 1970 Buick Skylark, was not a muscle car by definition. Being enterprising kids, my friend Charlie showed me that if you got under the hood and turned the air filter upside down, your car sounded like a muscle car. Performance was not good, but the car sounded like a beast on a rampage.
The parallel here is that if you get under the hood of the Content Query Web Part (CQWP), you can make this vibrant and still relevant Web Part scream with performance. We will do this with Overrides.
While the Content Search Web Part gets all the love and attention these days (rightfully so), the CQWP is in many cases the best tool for the job. From a functional standpoint, it is great at two things:
1. Aggregating information from anywhere in a site collection
2. Creating a business process
Having seen its use at many different companies, I get the opportunity to hear the many frustrations that come with it. The one frustration that I’ll address today is performance.
The CQWP is just a query to the back-end database. That means all the parameters it brings with its request (sites, filters, etc.) get passed to the database from the rendering of the Web Part. These parameters are accessible and configurable “under the hood” via Overrides.
In order to get under the hood, the first thing you must do is export the CQWP. That means inserting the Web Part on your page and selecting the option to export it. When you do, a file is created that you can then edit via Notepad. Contained within it are four Override properties:
1. ListsOverride
2. QueryOverride
3. ViewfieldsOverride
4. WebsOverride
These items override any options that you set via the edit parameters panel. The reason you want to utilize these for performance has to do with the ability to get granular control over what is being sent to and from the database. Let’s take a look at each one and how you can overcome frustration and enhance performance.
List Override
Querying a list via the GUI panel, you have three options as seen here:
What if you have a list and an archive of that list (two distinct lists), and you wish to aggregate them together? You don’t want to pick all lists and then try to filter that in some way down to two lists. That would be inefficient, pulling too many records. However, using the ListsOverride property, you can specify the lists you want using their GUIDs.
Query Override
When you specify getting your data, oftentimes you need to filter that data in some way. Shown in the following diagram, you can see through the edit panel that you can set three different filters. In the real world, filters are often more complex, and contain more than three filters and often embedded AND/OR logic.
Using the QueryOverride, you can write a filter that meets the complexity of your data. Obtain a CAML editor, and you can craft a query and modify the QueryOverride property, including sorting and order-by-field names. Not only does this give you granular control over the query, it is more efficient. Here is a complex example:
<property name=”QueryOverride” type=”string”><Where><And><Or><Eq><FieldRef Name=’Region’ /><Value Type=’Choice’>Global</Value></Eq><Eq><FieldRef Name=’Region’ /><Value Type=’Choice’>Europe</Value></Eq></Or><Geq><FieldRef Name=’EndDate’ /><Value Type=’DateTime’><Today/></Value></Geq></And></Where><OrderBy><FieldRef Name=’EventDate’ /></OrderBy></property>
View Fields Override
When a query runs against a list, there may be many fields. The user may only need to see four items from that list. Using the GUI edit panel, ALL fields are brought back. You do not specify the fields brought back in the edit panel; you can do this using the ViewfieldsOverride.
It allows you to override the default list fields names used to filter the set of list items. This brings A LOT of efficiencies (a.k.a. performance) when utilized. Think about it: When requesting information and you only need five fields instead of the 10 that normally come back, you reduce the database hit by 50%.
From first-hand experience, I have seen page load times drop by seconds on a page that had four CQWP, and we used this override on each one. It works great!
Webs Override
Looking at the edit panel for the query, we see that we can query whole site collections, a site and all its subsites, or one list. But there is no option to query from just a single site where we may have multiple lists we wish to aggregate. We could use filters, but again, that is inefficient.
Enter the WebsOverride property. By default the CQWP will pull data from all subsites, but when this option is set it will display only the lists from the specified site.
SharePoint is a “machine” of sorts, and we all want our sites to scream with performance. We want users to love the experience of seeing their aggregated data. That’s what brings value. Get under the hood and use these overrides to send your site into overdrive.
Peter Serzo is a published author of the “SharePoint 2010 Administration Cookbook,” a founder of the SouthEastern SharePoint group, a speaker, and SharePoint Architect for High Monkey Consulting.
