SharePoint List Throttling Issue in JSOM- Check Your CAML

Most of us are bound to come across this at some point in our SharePoint voyage! Architects and developers are more inclined towards using JavaScript Object Model as much as possible for SharePoint customization. And they are right, this would ensure that their applications would be upgraded to the next SharePoint versions with minimum or no changes.

We need to be very careful when designing solutions with JavaScript and consider many factors ahead of time. One important aspect that I would like to discuss here is about the SharePoint List Throttling as a part of Resource Throttling introduced in SharePoint 2010. SamlMan has discussed the topic of List Throttling for SharePoint 2010 in great details here.

Same is applicable for SharePoint 2013 , but there are few more options in terms of Add-ins (formerly known as Apps) to handle large lists.

I would like to discuss about how a small change in our CAML query can save us from hitting the list threshold limit while dealing with a large list using JSOM and CAML.

We had a custom solution for comments on a news article since we needed a reply-to-reply functionality. The solution was implemented by creating a separate discussion board for each of the news site (there were multiple news sites in the solution) in a separate site collection. So this site collection root site had multiple discussion boards, one per each news site.Each of the discussion board had unique permissions with contribute access for the reader group of the corresponding news site. We used JSOM with CAML to fetch the comments for a news page from the corresponding discussion board. This solution worked fine until one day comments stopped to show up on a news site's news page. On investigation, we found that the list threshold limit was not allowing the query to execute and hence no comments were returned for the page.Here is the original CAML query:

<View Scope="RecursiveAll">

    <Query>

        <Where>

            <And>

                <Gt>

                    <FieldRef Name="ID" />

                    <Value Type="Integer">' + _lastReply + '</Value>

                </Gt>

                <Eq>

                    <FieldRef Name="ParentFolderId" />

                    <Value Type="Integer">' + _discussionTopic.get_item('ID') + '</Value>

                </Eq>

            </And>

        </Where>

        <OrderBy>

            <FieldRef Name="ID" />

        </OrderBy>

    </Query>

</View>

Where

_lastReply is the id of the latest comment being posted on the news article.

_discussionTopic.get_item('ID') gives us the id of the discussion topic corresponding to the current news article. (Note that a separate site collection holds discussion board list that contain the comments for news pages in specific news site.)

Our analysis revealed that _lastReply variable was undefined for some reason for the first page load and it was working well when we tried to post two or more comments in the same browser session. And when we assigned a value to it in the debug mode during the first load (IE + F12 - you know it ), it worked well.

Challenge

So, the challenge was what would be the right value for this variable? After so much thinking , I concluded that the value for this variable has to be the Id of the discussion topic for the current news page. There is a specific reason for this: our code created a new discussion topic when a user first commented on the page and all the comments were added as a discussion item under this discussion topic in the corresponding discussion board.

Solution

Our solution was based on our architecture itself. Comments for each news page were stored under a separate discussion topic for the page. So if the page had only one comment for example, the design is such that the JavaScript would create a discussion topic and the comment itself where the discussion topic would be created first and then the actual comment would be added as a discussion item in the corresponding discussion board. Since both the discussion topic and the message are the items in the same list (they ave their own content types Discussion and Message respectively), discussion topic would always have a smaller Id than all the discussion items under it.

This was it! we capitalized on this and designed a solution for the issue.

We changed the query to the following:

We needed an additional variable, named as _controlVariable to be used in query, this variable actually helped us to keep the number of items below the list threshold limit. Additionally, we also limited the results to only return the items with Message content type.

var _controlVariable;
if (_lastReply)
{
    _controlVariable = _lastReply;
}
else
{
    _controlVariable = _discussionTopic.get_item('ID');
}
<
View Scope = "RecursiveAll" >
    <
    Query >
    <
    Where >
    <
    And >
    <
    And >
    <
    Eq >
    <
    FieldRef Name = "ContentType" / >
    <
    Value Type = "Text" > Message < /Value>
<
/Eq>
<
Eq >
    <
    FieldRef Name = "ParentFolderId" / >
    <
    Value Type = "Integer" > ' + _discussionTopic.get_item('
ID ') + ' < /Value>
<
/Eq>
<
/And>
<
Gt >
    <
    FieldRef Name = "ID" / >
    <
    Value Type = "Integer" > ' + _controlVariable + ' < /Value>
<
/Gt>
<
/And>
<
/Where>
<
OrderBy >
    <
    FieldRef Name = "ID" / >
    <
    /OrderBy>
<
RowLimit >
    5000
    <
    /Rowlimit>
<
/Query>
<
/View>

This worked like a charm! Another option would be to use set_folderServerRelativeUrl method of the query object, we would discuss about that in a separate post.

Conclusion

Always use <RowLimit> in CAML and look for clues in your implementation that would help you device some logic like we did with _controlVariable.