CQWP / CAML - Query returned a greater number of results than the web application's query throttling limit

Question

The Issue

I have a CQWP which appears to be configured to run with a CAML query (I'm a complete novice with CAML - I've inherited this set-up). It has been used successfully, until recently, to run a query to show documents Checked Out to [Me] throughout the site collection. The site collection contains one main site (barely used) and 12 sub-sites. There are a total of 7,679 documents that the query runs through.

My theory - I think the number of documents has reached a limit which makes it too difficult for the CAML Query to run? Perhaps there is a document limit? Bear in mind, the query runs over multiple sub-sites and multiple document libraries!

Here is the CAML query which I believe is being used.

RESEARCH

I've found some similar posts on the CQWP-CAML-throttling issue.

• Very similar question to mine here
• One relating to Tasks here

The Content Search Web Part (CSWP) is unavailable for me. I don't want to go fiddling with activating and deactivating a feature I don't fully understand the consequences of. The picture you see in this Question is the same as I see in the web part menu. Though this looks like an option for users of SP 2013 Enterprise (on Premises only(?)).

Extra Info

My predecessor left some notes:

Link to the source my predecessor used.

#CAML from 'Checked Out To Me' Content and Structure Report
<Where><Eq><FieldRef Name="CheckoutUser" LookupId="TRUE"/><Value Type="Integer"><UserID/></Value></Eq></Where>

#Line to be inserted in a pre-configured Content Query webpart to list Checked out documents to me: Replace 'QueryOverride' property with this:
<property name="QueryOverride" type="string"><![CDATA[<Where><Eq><FieldRef Name="CheckoutUser" LookupId="TRUE"/><Value Type="Integer"><UserID/></Value></Eq></Where>]]> </property>

Answer 1

My initial thought is that this must be by design- clearly the CAML for this particular query is not that complicated to envision or write, but there is also apparently a high performance cost to querying across an entire site collection's worth of document libraries.

My second thought is that it sounds like what you are looking for could be accomplished with the Search service. Since the CheckoutUser property is also a managed property in the search schema (CheckoutUserOWSUser), you can query directly on that property after it's been crawled, or even include it in a Result Source.

Below is a screenshot of the crawled properties I mapped to CheckoutUserOWSUser in the Search Service Application's Search Schema. Also remember to set the managed property as Queryable so that you can reference it by name from a Search Results web part.

Then, use a Search Results web part to display the results. Filter on {User.Name} (the name of the current user).

(The full Query text is "{searchTerms} CheckoutUserOWSUser:{User.Name}")

The advantage of this approach is that the search results are then held in the index, which means less stress on the system when you want to display them.

The disadvantage is that now your "Checked out to me" page is dependent on the index being up to date, so you may need to enable Continuous Crawl on your Content.

If you have a well-appointed farm, this may be especially feasible, since you could isolate the search service components in a separate server and shorten the continuous crawl interval.

Finally, I am also curious if changing either of the below settings for the Web Application would have any effect on your CQWP:

• List View Lookup Threshold
• HTTP Request Monitoring and Throttling

I am not sure if these two settings are directly related to the error message you're seeing, but it might be worth it to try, especially considering the "Checkout out to" column is itself a lookup to the User List.

Answer 2

I think the problem was caused by a custom web part (one of the two below is a custom WP, I believe the other is OOTB.

The custom web part was made by using this blog post. It worked for a few years, until very recently. Therefore I think over time, as new documents were saved in the site collection, there were too many documents for the web part to 'query' through - so it went over some kind of limit (I don't exactly understand it). To conclude, don't use what the blog post suggests! There are comments at the end of the blog post where other users have reported the same issue as me!

My thanks to Chris and Marek for posting Answers, this helped me to narrow things down and gave ideas about possible reasons. Given my initial confusion when I posted the question I thought the issue had something to do with List View Threshold or Throttling

In the end I have replaced the defective custom web part with a standard Content Query, as below:

I'd read that the Link/Image//Title/Description fields can be changed to different properties - so I changed the Description to Checked out User, this allows the Accounts team to see that a file is checked out to a named person.

The standard OOTB CQWP shows these results you see in the picture below. You'll note that not all files show the name of the person the file is checked out to...so the issue is not resolved yet... I'll work on this and see if I can figure it out (I need to restart my computer to see if that will help)

Answer 3

Good point on the search approach from Chris. One thing though changing the list view lookup threshold will not change the behavior. The crux of the problem is that the list view threshold that has 5000 item limit (in central admin app settings). Do not

But don' t don't do that as it will have performance impact (or do it if you can live with perf impact) the usual problem is that admin constantly increases that limit, until a point where the farm isn't usable any longer.

The correct way to deal with that is to create an index on the checked out to column. In the below blog you have an detailed procedure. But it goes like that:

1. Increase the list view threshold or create the daily window for large queries.
2. Create indexes on the checked out to column in all the libraries.
3. Reduce the list view threshold to 5K.

REF: https://blogs.msdn.microsoft.com/mcsnoiwb/2011/11/18/how-to-list-checked-out-documents-when-exceeding-the-list-view-threshold/