SharePoint REST and Large Lists

As you may have discovered there is a limit on the size of lists in SharePoint.

Well that is not strictly true, the amount of items can be pretty large (30 million or so).  However, you may have received a message:

This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.

The number of items a single view can return is set to 5000 items by default.

You may be returning what you think is way less than that but still get this pesky error.  So what do you do?

Well you could increase the threshold in Central Admin.  But you may not have access to this, or you may realise that this is limited for a reason, and changing such things ‘willy nilly’ is setting the table and pulling up a chair for Mr Cockup!

Better to tackle the issue at source.  So what can you do?

  1. Index your columns
    Yes – this is the first thing you should do if you foresee large numbers of items.  Identify which columns will slice and dice your data most efficiently and get them indexed before your list starts filling up.
  2. Design your Queries / Views
    Make sure that your views (these are ultimately CAML queries) use these indexed columns efficiently.  If you are not using an indexed column in your query then it’s pulling everything back from the container before querying the data.
  3. Utilise Containers
    The view limit is per container – a container can be a view or a folder.  This can be a case for using folders if appropriate.  Folders may be the anathema of what SharePoint is about(!) but here they may avoid some headaches.  Just remember when using Recursive and RecursiveAll in your queries that you’ll be losing their protection.

My query uses an indexed field but I still get the error!

The order matters.  If you are querying on more than one field and not using a compound index then the first value in your query will be used to query the database, following retrieval the remaining filters will be applied.  If this first query brings back more than 5000 items back from the database it will hit the threshold.  So if you’re using an AND put the filter with the most significance as the first AND parameter.

I want to get (much) more than the threshold back for my custom report web part…

You may require to pull back thousands of items for a one off report or such like and knowingly want to exceed for this purpose – e.g. a report.

Not to worry – REST to the REST-cue.

If you’re using REST calls to populate your data client-side you can encapsulate a calls within a single function for this type of requirement.  Here’s an example using jQuery deferred objects and promises.

function getBigList(listId, data, url, dfd) {

    // deal with empty parameters    
    dfd = dfd || $.Deferred();
    data = data || [];
    url = url || _spPageContextInfo.webAbsoluteUrl 
            + '/_api/lists/getbyid(\'' + listId + '\')/items?$OrderBy=ID&$top=5000';

    // make sure digest up to date (for long operations)
    UpdateFormDigest(_spPageContextInfo.webServerRelativeUrl, _spFormDigestRefreshInterval);

    jQuery.ajax({
        url: url,
        type: 'GET',
        aync: true,
        headers: {
            'Accept': 'application/json;odata=verbose',
            'X-RequestDigest': $('#__REQUESTDIGEST').val()
        },
        success: onSuccess,
        error: onError
    });

    function onSuccess(response) {
        if (response.d && response.d.results) {
            $.each(response.d.results, function (index, resItem) {
                data.push(resItem)
            });

            // check for more pages
            if (response.d.__next) {
                // there are more so recursively call with next page url (response.d.__next)
                // and pass the data and deferred object
                getBigList(listId, data, response.d.__next, dfd);
            }
            else {
                // now we have all
                dfd.resolve(data);
            }
        }
        else resolve([]);
    }

    function onError(error) {
        dfd.reject('getBigList: ' + getErrorMessage(error));
    }

    return dfd.promise();
}

The key here is that the parameter $top is used to specify the upper limit to what the call can return, additionally the results must be sorted by ID – the default, unqueried data.  If the $top number is exceeded then the first ‘page’ is returned along with a URL to the subsequent page.  This can be called recursively until all the data is received.

Call this in the following way:

getBigList(LIST_ID)
    .done(function (data) {
        $.each(data, function (index, dataItem) {
            //dataItem.stuff......
        });
    })
    .fail(function (msg) {
        console.log(msg);
    });

So by being mindful of what SharePoint is doing server-side and structuring our lists and queries thoughtfully we can get around some of the limitations in an efficient way.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *