Fetching the last updated items from a large list

I have a list with over 5000 items and on a regular interval I want to query the list to retrieve the last modified items since last time. Typically I will poll every 15 minutes with a CAML query like this:

<View Scope='RecursiveAll'>
    <Query>
        <Where>
            <And>
                <Eq>
                    <FieldRef Name='ContentType'/>
                    <Value Type='Computed'>Item2</Value>
                </Eq>
                <Geq>
                    <FieldRef Name='Modified' />
                    <Value IncludeTimeValue='True' StorageTZ='TRUE' Type='DateTime'>2018-01-15T14:24:00Z</Value>
                </Geq>
            </And>
        </Where>
    </Query>
    <RowLimit>5000</RowLimit>
</View>

This query works fine up till the list reached 5000 items and you will get the infamous item threshold error. In my case there will never be over 5000 updated items in my polling timespan so the fix it quite simple. As I limit on the modified date, add an index for the Modified field from the list settings page. Problem solved, queries will work. And to be sure, create your indexes up front – makes it a lot easier.

An alternative approach [update]

As pointed out by iOnline247 on twitter, an alternative approach is to use the GetChanges API for a list. The GetChanges function can take a specific query in, and you get a change token back which you can use on subsequent calls to get new items since your last call. This would entail storing the token somewhere, which is not a big deal, but will introduce another part to the solution and a little bit of complexity. My current solution is a scheduled powershell script and very stateless. It doesn’t matter if I get an item twice for example.

A third option, which is what I’m moving over to is using Microsoft Flow to trigger on changes on the list.

Photo by Samuel Zeller on Unsplash