SharePoint 2013: CAML Query that contains more than 60 values for the "In" clause is throttled
Consider the following scenario.
In SharePoint 2013, a CAML query is issued against a list:
· The security context is a user who is not an administrator.
· 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>