none
The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator - ECMAScript reading list items RRS feed

  • Question

  • I am getting this error when I try to read the items from a list which has 27540 items. I am using simple ECMAScript object model code to get the list items as shown below.

    var list = web.get_lists().getByTitle("QuarterlyEvaluations");
            var camlQuery = new SP.CamlQuery();
            var q = "<View><Query><Where><And><And><Eq><FieldRef Name='QuestionnaireID' /><Value Type='Lookup'>"+questionID+"</Value></Eq><Eq><FieldRef Name='Evalution' />"+
            "<Value Type='Lookup'>"+evaluationID +"</Value></Eq></And><Eq><FieldRef Name='Hotel' /><Value Type='Lookup'>"+hotelID+"</Value></Eq></And></Where></Query><RowLimit>1</RowLimit></View>";
            
            camlQuery.set_viewXml(q);
            this.evaluationItems = list.getItems(camlQuery);
            clientContext.load(evaluationItems, 'Include(ID,Answers)');
            clientContext.executeQueryAsync(Function.createDelegate(this, this.OnQuarterlyEvalutionLoadSuccess),
                    Function.createDelegate(this, this.onQueryFailed));
    
    


    I am querying for only one record and I don't need all records from the list. So, I have set Rowlimit and get only 2 fields data in the result. I tried all ways but the error still coming. 

    I have increased List View Threshold value to 30000 for testing in central administration and still no luck. What could be the issue?

    thanks


    ASP.NET and SharePoint developer
    Company: http://www.rampgroup.com/
    Blog: http://praveenbattula.blogspot.com
    Saturday, November 5, 2011 7:33 AM

Answers

  • It's rather pathetic that the only real solution to the problem is to effectively disable thresholding; trying to use any list under the threshold more than just minimally is almost impossible.

    Read the last section of this link (How Does Indexing Affect Throttling?) for an explanation as to the underlying problem.  Basically, in order to query a list with more items than the throttle limit you need to filter on a field that is indexed, and the results of that filter need to be less than the throttle limit.  If the field isn't indexed, or if the first field that is filtered on (by itself) doesn't put you below the limit then you can't perform the query.  The link explains how setting the rowlimit to something below the throttle limit doesn't actually help get around it in all but a few cases.

    That said, there are a few other ways to get around the limit:

    1. As suggested, you can set the limit to be something high enough that you never hit it on a per-Web-App basis.
    2. You can use the SPList.EnableThrottling property and set it to false for the list(s) in question.  This needs to be done in code, using the server object model, and from a highly privileged user, but it's also a one time change as it will persist.  One option is to just make a feature that sets this property for your list(s) to the desired value.
    3. You can use the SPQuery QueryThrottleMode property.  There are a few things here.  First, this doesn't disable throttling, it just increases the limit.  In central admin you can set the limit for regular users and also super users.  By default, the limit when using this override is 20,000.  For your case that won't be enough, so you could potentially increase the limit for super users without increasing the limit for all of the other cases.  Additionally setting the QueryThrottleMode to Override isn't enough; the user needs to be a super user.  This often means using privilege escalation (which I don't think you can do using the Client Object Model).
    This is what I've learned from my experimentation with throttling in the past few days on a project that I'm working on.  If anyone has any additional insights or more effective means of dealing with throttling (that don't just involve getting rid of it entirely) I'd love to hear them.
    • Edited by servy42 Monday, November 7, 2011 8:50 PM
    • Proposed as answer by HeToC Monday, November 7, 2011 8:59 PM
    • Marked as answer by Praveen Kumar Reddy B Tuesday, November 8, 2011 5:33 AM
    Monday, November 7, 2011 8:49 PM
  • Hi Praveen,

    The resolution to this error is:

    1. Navigate to Central Administration.

    2. Go to Application Management > Manage Web Application and click on your web application to select it.

    3. In the Ribbon, click on General Settings drop-down and choose “Resource Throttling”.

    4. In the “List View Threshold”, increase the value (by a factor of 2, for example) and click OK.

    5. Try to replicate the error.  If the error persists, increase the value again until the error goes away.

    For more information,please refer to:
    http://sharepointnomad.wordpress.com/2011/05/04/solving-the-attempted-operation-is-prohibited-because-it-exceeds-the-list-view-threshold-error/

    http://www.akshaykoul.com/blog/Home/tabid/40/EntryId/10/List-Throttling.aspx

    Thanks,
    Simon


    Monday, November 7, 2011 6:09 AM

All replies

  • The items are huge means 27K+ but they will grow. I cannot apply the filters or index columns as the fields in list are 5 Lookup fields and 2 string fields and 1 number field. I am not having idea how to get the problem solved.

    thanks


    ASP.NET and SharePoint developer
    Blog: http://praveenbattula.blogspot.com
    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you.
    Saturday, November 5, 2011 8:16 AM
  • Seems like there is no other way than increasing threshold to max. But, it will grow in future. Might be restructuring of data is the only solution. 

    Even from SharePoint object model Override Query Throttling mode is also not working


    ASP.NET and SharePoint developer
    Blog: http://praveenbattula.blogspot.com
    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you.
    Saturday, November 5, 2011 2:31 PM
  • Hi Praveen,

    The resolution to this error is:

    1. Navigate to Central Administration.

    2. Go to Application Management > Manage Web Application and click on your web application to select it.

    3. In the Ribbon, click on General Settings drop-down and choose “Resource Throttling”.

    4. In the “List View Threshold”, increase the value (by a factor of 2, for example) and click OK.

    5. Try to replicate the error.  If the error persists, increase the value again until the error goes away.

    For more information,please refer to:
    http://sharepointnomad.wordpress.com/2011/05/04/solving-the-attempted-operation-is-prohibited-because-it-exceeds-the-list-view-threshold-error/

    http://www.akshaykoul.com/blog/Home/tabid/40/EntryId/10/List-Throttling.aspx

    Thanks,
    Simon


    Monday, November 7, 2011 6:09 AM
  • It's rather pathetic that the only real solution to the problem is to effectively disable thresholding; trying to use any list under the threshold more than just minimally is almost impossible.

    Read the last section of this link (How Does Indexing Affect Throttling?) for an explanation as to the underlying problem.  Basically, in order to query a list with more items than the throttle limit you need to filter on a field that is indexed, and the results of that filter need to be less than the throttle limit.  If the field isn't indexed, or if the first field that is filtered on (by itself) doesn't put you below the limit then you can't perform the query.  The link explains how setting the rowlimit to something below the throttle limit doesn't actually help get around it in all but a few cases.

    That said, there are a few other ways to get around the limit:

    1. As suggested, you can set the limit to be something high enough that you never hit it on a per-Web-App basis.
    2. You can use the SPList.EnableThrottling property and set it to false for the list(s) in question.  This needs to be done in code, using the server object model, and from a highly privileged user, but it's also a one time change as it will persist.  One option is to just make a feature that sets this property for your list(s) to the desired value.
    3. You can use the SPQuery QueryThrottleMode property.  There are a few things here.  First, this doesn't disable throttling, it just increases the limit.  In central admin you can set the limit for regular users and also super users.  By default, the limit when using this override is 20,000.  For your case that won't be enough, so you could potentially increase the limit for super users without increasing the limit for all of the other cases.  Additionally setting the QueryThrottleMode to Override isn't enough; the user needs to be a super user.  This often means using privilege escalation (which I don't think you can do using the Client Object Model).
    This is what I've learned from my experimentation with throttling in the past few days on a project that I'm working on.  If anyone has any additional insights or more effective means of dealing with throttling (that don't just involve getting rid of it entirely) I'd love to hear them.
    • Edited by servy42 Monday, November 7, 2011 8:50 PM
    • Proposed as answer by HeToC Monday, November 7, 2011 8:59 PM
    • Marked as answer by Praveen Kumar Reddy B Tuesday, November 8, 2011 5:33 AM
    Monday, November 7, 2011 8:49 PM