SharePoint CAML query - order of Where conditions giving different results

Question

I am experiencing issues in SharePoint Online with a large document list which contains thousands of records. I am getting the usual list view exceeds 5000 items error for some views, so I am trying to filter these views by a date field. I am seeing some strange results that change based on the order of my where conditions.

When I order by the "Event Date" AND "Division" fields, the query is successful.

<View Scope=\"RecursiveAll\">
<Query>
    <OrderBy Override='TRUE'><FieldRef Name='ID' /></OrderBy>
    <Where>
        <And>
            <Geq><FieldRef Name='Event_x0020_Date' /><Value Type='DateTime'>2019-01-01T00:00:00Z</Value></Geq>
            <Eq><FieldRef Name='Division' /><Value Type='Text'>{division}</Value></Eq>
        </And>
    </Where>
</Query>

When I perform the exact same query, but the conditions reversed, I get an error that the view exceeds the list view threshold.

<View Scope=\"RecursiveAll\">
<Query>
    <OrderBy Override='TRUE'><FieldRef Name='ID' /></OrderBy>
    <Where>
        <And>
            <Eq><FieldRef Name='Division' /><Value Type='Text'>{division}</Value></Eq>
            <Geq><FieldRef Name='Event_x0020_Date' /><Value Type='DateTime'>2019-01-01T00:00:00Z</Value></Geq>
        </And>
    </Where>
</Query>

It seems to me that SharePoint determines that the first condition is already over 5000 items and just does not bother with the 2nd condition. Am I missing something?

teylyn teylyn · Accepted Answer · 2019-09-03T21:08:50

In a SharePoint query you will hit the 5000 item threshold if the first filter returns more than 5000 items. Each condition will be evaluated individually, before the second condition of the AND is applied.

If you have more than 5000 items that have the same division, then you can't use that as the first condition. But if the event date applies to fewer than 5000 items, you can use that as the first condition and then add the division condition with AND.

So, yes, the order of the conditions does matter.

SharePoint CAML query - order of Where conditions giving different results

1 Answers