So I am building a webpart which enumerates all doclibs in the current web, grabbing all checked out files and listing them in a table, allowing a user to apply some required metadata and check in many at once (50).

This webpart must operate on lists over the list view threshold (currently set at 15,000 in production), and also on sites that are VERY large (50,000 - 100,000+ documents).

I'm following the MS Best practices for handling large lists here: http://msdn.microsoft.com/en-us/library/ee557257.aspx

Using an SPquery (with no CAML defined at all), retrieving pages of 2,000 items and parsing that way. The problem with this is, that the webpart is actually causing a timeout to occur on those very large (50k+) sites. So I'm trying to be a bit smarter with my CAML, pulling in only items that are checked out:

spQuery.Query = "<Where><IsNotNull>
    <FieldRef Name=\"CheckoutUser\" LookupId=\"TRUE\"/>
spQuery.RowLimit = 2000;
spQuery.ViewAttributes = "Scope=\"Recursive\"";

I'm using a CrossListQueryInfo query to query the whole web with this same caml, and it works wonderfully when no list is over the LVT. If one is, I catch that exception and re-try with the 'slower' SPQuery on each individual library.

From everything I am reading, as long as my CAML is returning less items than the LVT, it should work. But using the CAML above causes the error The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator to be thrown when SPList.GetItems(spQuery) is called. Since I'm setting a rowlimit of 2,000, shouldn't that never happen? MS Suggests to execute an SPQuery with no CAML defined at all - basically grabbing all items from the library in pages of 2,000. So I can't make any sense of why my CAML is failing only on lists over the view threshold.

Edit: Upon further research, I am attempting to utilize the ContentIterator class to meet my needs (http://msdn.microsoft.com/en-us/library/microsoft.office.server.utilities.contentiterator.aspx). Using examples from this post: http://extreme-sharepoint.com/2012/07/17/data-access-via-caml-queries/

I am still failing on the contentiterator with the same LVT error.

Does the 'CheckoutUser' field need to be indexed on every list we want to execute this query against?

Update 2: This comes down to the 'CheckoutUser' field not being indexed, and trying to query against it. Unfortunately it is not an option for us to go out and force this index on every library in the farm. I believe my only option at this point is to implement some sort of paging scheme on very large sites to process items in batches.

Final Update: As a solution, I've decided to enforce indexing the 'CheckoutUser' column for libraries. This should vastly improve the overall performance of the webpart, and allow support for very large sites. There will be a bit of headache immediately following deployment, as we will need to manually set the column index on lists over the list view threshold, but in the long run this will be best.


2 Answers 2


My understanding of how the List View Threshold works is limited, however, I suspect the reason your CAML query is failing is because it is filtering on the non-indexed field, and the RowLimit is applied "after".

I think that indexing the CheckoutUser field would solve your problem, however it sounds like you have a lot of sites.

Allow me to suggest an alternate approach - paging. Since the ID column on every list is indexed, implement a CAML query where you are filtering FIRST (very important) on the ID column less than, say, 2000, and secondly on the CheckoutUser field. If that query does not return the desired number of results, then increase 2000 to 4000 and repeat.

I have not implemented this solution myself, it is just a thought.

  • Yes, indeed it is the non-indexed field causing an issue. By indexing the 'CheckoutUser' field, the ContentIterator succeeds. You have a good idea. My last resort was going to remove the SPQuery altogether and put paging right into the webpart, but I think we can do it more elegantly by executing multiple queries against the ID field. Basically just keep executing and incrementing the ID range by 2,000 until a query returns 0 results.
    – Sean
    Commented Mar 20, 2013 at 18:34
  • Would you be able to briefly explain to me how to implement a CAML query in such a way that ID is filtered first, and then Checkout User second?
    – Sean
    Commented Mar 21, 2013 at 13:08
  • Just make the ID filter first in the CAML.
    – Chloraphil
    Commented Mar 21, 2013 at 13:20
  • Late, but thanks a lot! I had no idea on how to query my large library. As for a CAML example, here we go: <View><Query><Where><And><Contains><FieldRef Name='FileLeafRef' /><Value Type='File'>MyDocumentName</Value></Contains><And><Leq><FieldRef Name='ID' /><Value Type='Counter'>4000</Value></Leq><Gt><FieldRef Name='ID' /><Value Type='Counter'>2000</Value></Gt></And></And></Where></Query></View>. It's not Op's query though.
    – Kilazur
    Commented Aug 27, 2015 at 8:01
  • Imagine items 2000 to 4000 are deleted. In this case, the second query will return 0, and items 4000+ will never be queried. An alternative is to get the Max ID (sort by ID desc, page size 1), then request pages all the way up to that ID. Commented Dec 16, 2015 at 10:14

If you are querying large lists, you should definitely index the fields on which you are querying. Throttling does not occur because the indexed fields are stored in a separate table in the database.

This blog post of mine should be of some help I think: http://vrdmn.blogspot.in/2012/11/sharepoint-list-indexes-under-hood.html

  • I agree that indexed columns do seem to be the issue here, the problem is that this webpart will be used across an enterprise environment consisting of 70,000+ users and 15,000+ site collections - we can't really go around automatically indexing the CheckoutUser column on user sites. Thank you for the link to that blog post. I am still fairly new when it comes to working with large lists.
    – Sean
    Commented Mar 20, 2013 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.