0

I'm needing to know what the exact nested CAML Query limits are for each version of Sharepoint? Also are these limits the same for using the IN, INCLUDES Operators with 2010+ ???

0

This is for Sharepoint 2013. When a CAML query is issued against a list:

  • if the security context is a user who is not an administrator

  • and the query contains a filter that uses the "IN" clause.

  • More than 60 values are passed for a particular indexed field that
    includes the "In" clause.

The query is throttled, and the following error message is shown to the user and logged in the ULS logs:

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator

Cause

In SharePoint 2013, a design change was made in the query builder component to limit the maximum number of values for the "In" clause to 60. This is because with more than 60 values that are passed in the "In" clause, the corresponding SQL query becomes complex and may result in a table scan that results in overall decrease in performance of the site. When more than 60 values are passed with the "In" clause, the field for which the values are passed is considered a nonindexed field, and the operation may be throttled.

Resolution

When issuing a filtered CAML query against a list, do not pass more than 60 values with the "In" clause. Divide the operation into batches of 60 or less so that each query contains less than 60 values with the "In" clause for any given indexed field. The limit of 60 values with the "In" clause is currently not configurable.

More Information

CAML queries may be issued by using CSOM, REST or the SharePoint Server object model. The behavior explained in this article applies to all interfaces available to issue a CAML query. Here is an example of a filtered CAML query that passes three values with the "In" clause for the Document Id field.

<View Scope="RecursiveAll">
  <Query>
    <Where>
        <In>
          <FieldRef Name=’_dlc_DocId’ />
          <Values>
            <Value Type=’Text’>CSODD-14-42468</Value>
            <Value Type="Text">CSODD-14-40667</Value>
            <Value Type="Text">CSODD-14-39168</Value>
          </Values>
        </In>
    </Where>
  </Query>
  <QueryOptions>
    <IncludeMandatoryColumns>True</IncludeMandatoryColumns>
  </QueryOptions>
</View>

Referenced from MSDN blog post here

  • Thanks for the comment. Do you know what the limit is for nested OR query's for each version of Sharepoint? – adha409 Jun 20 '16 at 17:07
  • Sorry, can't say for sure. There is really not a lot of documentation on this. The common view however is to process in batches. – Ransher Singh Jun 20 '16 at 17:12
  • Is this also true when splitting multiple IN clauses in a single query? e.g. a single query that has 3 IN clauses each with 30 conditions -- I assume this would not be a valid work-around and would still throw the >60 error? – ferr Apr 6 '17 at 15:09
  • @ferr I would think so although, to be frank, I have not tested that scenario out. – Ransher Singh Apr 6 '17 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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