SharePoint 2010 Content Query Web Part demystified

This blog is more about architecture & information management in a real world SharePoint environment. Since content aggregation is part of architecture & information management I decided to share my findings on the elusive Content Query Web Part a.k.a. CQWP.

The story
According to Microsoft the CQWP can be used to show aggregate content:

• over a single list
• over a site and all subsites
• over a site collection

The story demystified
The above mentioned is accurate...although not complete.
Imagine this real world scenario. Contoso is a medium sized company with approx 25.000 documents. These documents reside in a single Site Collection and are spread over multiple document libraries. Off course we implemented a thorough information architecture and this all documents are neatly classified in terms of content type and metadata.

Now we have a business requirement: "I want to see all contracts assigned to me.".
Further, an architectural principle states only OOB functionality is allowed.

And we are off.... Let's skip a couple of steps to the point the CQWP is the way to go for this requirement (OOB it is... really... trust me... no you cannot use search).

The CQWP: What does it do and where can I find it?
The CQWP is an out of the box webpart that, as stated above, enables you to aggregate data. We need aggregation over multiple libraries and we need filtering. Before this little 'gem' makes it appearance you need to enable it. This is done via the activation of the publishing infrastructure feature.

Analysis
The contracts are stored in several different libraries. Perhaps due to shere numbers, authorization, etc.

Implementation
So we create a new page, add the CQWP.
Next part is the configuration. The 3 elements we are going to focus on here are:

• Source
• Filter
• Sorting

First off the source. Since we are testing the solution we are setting the source to aggregate over a single list.
Content Type = Contract
The filter is DocumentManager (people picker field type) and it's value is set to '[Me]'.

Run the page and voila we have a result of 2 contracts (just an example). As a wise man once said: WOOHO! We have a(n almost) working solution!

Now, change the source of the filter to aggregate everything over the site collection and:
CRASH!

What just happened? Where is my nicely aggregated list of contracts?

CQWP internals
At this point we need to start debugging the solution. Review filter settings, (if you didn't already) enable trace logging, etc. etc.

As you perhaps already know the CQWP runs a CAML query that you probably want to analyze. Glyn Clough has written a nice blog on just how to do that! Off course we are using the ULS Viewer to analyze  the huge amounts of ULS logs created. Somewhere in there you will find something like:
"xxxxxxx* w3wp.exe (0x0C98) 0x16F0 Web Content Management Publishing 7352 Warning ...entTypeId" Nullable="True" Type="ContentTypeId"/><Value Type="ContentTypeId">0x0101</Value></BeginsWith><Eq><FieldRef ID="{f366697d-21a6-493e-af7d-9b3cf5410ea4}" Nullable="True" Type="User"/><Value Type="User"><UserID/></Value></Eq></And></Where><OrderBy></OrderBy></Query>' generated the following error:The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator. at the following url: XXXXX. Web Part title: Content Query b8210847-657f-40f3-80af-22bf444ad8f8"

The good, the bad and the ugly
The good part about this log is that it is extremely clear and straightforward! The CQWP is prohibited from displaying the results because it exceeds the set limitation of the list view threshold (LVT). As you can read in SharePoint 2010 capacity management the default value of the lvt is 5.000 items.

The bad part about this is that this setting isn't there because it looks really cool but it has a real purpose. Try cranking it up to, let's say, 30.000 and let half a dozen users try to access the page. You can actually see the performance penalty it causes.

The ugly part... now what? The CQWP should only return a couple of contracts, not even close to 5.000.

Indexed columns
As it turns out the columns you use with filtering AND sorting need to be indexed columns, according to Microsoft.

The ultimate catch
Try and add more than 5.000 items in a single document library, filter on a single column (using the CQWP) and give it a shot! When you use an indexed column as a filter it will return results. When you remove the indexed column from that specific list it will not return results and spit back the log mentioned above.

So, the solution is adding the filter column on all document libraries in the site collection and....CRASH! WTF? We did everything correct and still nothing.

"Statement" by Microsoft
(This is not the actual statement but a, valid, free interpretation)
The CQWP only uses the indexed columns when selecting a single list as source. The CQWP ignores the indexed columns when the source is anything else than a single list!

And that my friends is how the cookie crumbles!

Sources:
Designing large lists and maximizing list performance (SharePoint Server 2010)
Data in SharePoint 2010 – Part 2 – Content Query Web Part
SharePoint Server 2010 capacity management: Software boundaries and limits
Inspecting the caml of a content query web part