none
How can I overcome limitations of list view threshold when querying SharePoint 2013 Online? RRS feed

  • Question

  • I have a document library that holds more than 5000 items and what I'm trying to do it's retrieve all items from one particular folder form my SharePoint Application. I had tried code as shown below and got error "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator".

    itemsInFoldersCollection = list.GetItems(new CamlQuery() 
    { 
    	ViewXml = string.Format(
    	"<View Scope=\"RecursiveAll\">"+
    		"<Query>"+
    			"<Where>"+
    				"<Eq>"+
    					"<FieldRef Name=\"FileDirRef\" />"+
    					"<Value Type=\"Text\">{0}</Value>"+
    				"</Eq>"+
    			"</Where>"+
    		"</Query>"+
    	"</View>", 
    	strServerRelativeUrl) 
    });
    Context.Load(itemsInFoldersCollection);
    Context.ExecuteQuery();  
    I've found out that common practice to use ContentIterator Class from Microsoft.Office.Server.Utilities, but looks like there is no any analog in Microsoft.SharePoint.Client. So how can I retrieve all items from folder by series of queries or how can I retrieve items partially in one single query? Thanks in advance.
    Tuesday, October 9, 2012 3:56 PM

Answers

  • For large lists, we need to specify the

    • OrderBy: It should use the indexed fields and specify the Override=”TRUE”. If view specified Scope=”RecursiveAll”, we should use <OrderBy Override=""TRUE""><FieldRef Name=""ID"" /></OrderBy>. If we only want to retrieve the items under a folder, we should use use <OrderBy Override=""TRUE""><FieldRef Name=""FileDirRef"" /><FieldRef Name=""FileLeafRef"" /></OrderBy>
    • RowLimit: It should be less than the server’s throttling settings. The recommendation is to use some RowLimit less than 1000.
    • ViewFields: It should not be empty and it should not include more than 8 lookup fields. If it is empty, the server will return data for all columns. If the list contains a lot of lookup fields, the query will be throttled.

    For example, we could use:

                query.ViewXml = @"<View Scope=""RecursiveAll""><Query><OrderBy Override=""TRUE""><FieldRef Name=""ID"" /></OrderBy></Query><ViewFields><FieldRef Name=""Title"" /><FieldRef Name=""Modified"" /><FieldRef Name=""Editor"" /></ViewFields><RowLimit Paged=""TRUE"">30</RowLimit></View>";

    Or if we want to retrieve items from a folder

                query.ViewXml = @"<View><Query><OrderBy Override=""TRUE""><FieldRef Name=""FileDirRef"" /><FieldRef Name=""FileLeafRef"" /></OrderBy></Query><ViewFields><FieldRef Name=""Title"" /><FieldRef Name=""Modified"" /><FieldRef Name=""Editor"" /></ViewFields><RowLimit Paged=""TRUE"">30</RowLimit></View>";

                query.FolderServerRelativeUrl = “/Lists/Announcements/Folder1”;

    Thursday, December 6, 2012 6:36 PM

All replies

  • Have you tried using:

    <RowLimit>#Number</RowLimit>

    in your query?  You would probable want to order by ID and then add a greater than statement for the ID on subsequent queries.


    -James
    MCPD: SharePoint Developer 2010
    Posting is provided "AS IS" with no warranties, and confers no rights.
    Please mark as answer or helpful as appropriate

    Tuesday, October 9, 2012 5:31 PM
  • James, thank you for reply. So I've changed code like this

    itemsInFoldersCollection = list.GetItems(new CamlQuery() 
    { 
    	ViewXml = string.Format(
    	"<View Scope=\"RecursiveAll\">" +
    		"<RowLimit>100</RowLimit>"+
    		"<Query>" +
    			"<OrderBy>" +
    				"<FieldRef Name=\"ID\"/>" +
    			"</OrderBy>"+
    			"<Where>" +
    				"<Eq>" +
    					"<FieldRef Name=\"FileDirRef\" />" +
    					"<Value Type=\"Text\">{0}</Value>" +
    				"</Eq>" +
    			"</Where>" +
    		"</Query>" +
    	"</View>",
    	strServerRelativeUrl) 
    });
    Context.Load(itemsInFoldersCollection);
    Context.ExecuteQuery(); 

    But still get exactly the same error "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator".
    Tuesday, October 9, 2012 7:08 PM
  • Try moving RowLimit to the bottom.  Check this out:

    http://msdn.microsoft.com/en-us/library/jj163201(v=office.15).aspx#BasicOps_SPListItemTasks


    -James
    MCPD: SharePoint Developer 2010
    Posting is provided "AS IS" with no warranties, and confers no rights.
    Please mark as answer or helpful as appropriate

    Tuesday, October 9, 2012 7:18 PM
  • I've tried. Nothing changed.
    Tuesday, October 9, 2012 7:46 PM
  • could you try it with just:

    <View><RowLimit>5</RowLimit></View>

    to ensure there is not a problem anywhere else in the query?  Otherwise, it might be a bug because everything I am seeing says it should work.


    -James
    MCPD: SharePoint Developer 2010
    Posting is provided "AS IS" with no warranties, and confers no rights.
    Please mark as answer or helpful as appropriate

    Tuesday, October 9, 2012 7:55 PM
  • James, thanks for the tip. It works when i place only the string that you gave to ViewXml. So looks some part of my query causes the issue. I just wonder which one.
    Tuesday, October 9, 2012 8:30 PM
  • I've found the problem. It's in

    <View Scope=\"RecursiveAll\">

    When set View without any attributes everything works as it should. Not sure, but maybe it is a bug in SharePoint. Of course I can workaround this thing, but it's pity that I can't use RecursiveAll.
    Tuesday, October 9, 2012 8:53 PM
  • Glad it was something small (but isn't it always).  

    BTW - did you see the static methods "CreateAllFoldersQuery" & "CreateAllItemsQuery"   http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.client.camlquery_members.aspx

    Looks interesting for your needs.

    Happy Coding!


    -James
    MCPD: SharePoint Developer 2010
    Posting is provided "AS IS" with no warranties, and confers no rights.
    Please mark as answer or helpful as appropriate

    Tuesday, October 9, 2012 9:07 PM
  • I was too much excited with gone Exception yesterday and completely missed that I receive an empty collection. The reason why I prefer to use CAML over "CreateAllFoldersQuery" & "CreateAllItemsQuery" is ability to retrieve items form several folders by using AND element in my query. But obvious something still wrong in it.
    Wednesday, October 10, 2012 4:09 PM
  • Hi,

    I just faced this issue too with  SP.CamlQuery.createAllItemsQuery() in JS. Setting  RowLimit with listItemCollectionPosition doesn't help. And I'm getting same error, querying this list with OData.

    https://<my domain>/sites/<my site>/_api/web/lists/getByTitle('<my library>')/Items?$top=1000

    Any Ideas how to overcome this issue? Thanks.


    thank you :)

    Wednesday, October 17, 2012 3:15 PM
  • For large lists, we need to specify the

    • OrderBy: It should use the indexed fields and specify the Override=”TRUE”. If view specified Scope=”RecursiveAll”, we should use <OrderBy Override=""TRUE""><FieldRef Name=""ID"" /></OrderBy>. If we only want to retrieve the items under a folder, we should use use <OrderBy Override=""TRUE""><FieldRef Name=""FileDirRef"" /><FieldRef Name=""FileLeafRef"" /></OrderBy>
    • RowLimit: It should be less than the server’s throttling settings. The recommendation is to use some RowLimit less than 1000.
    • ViewFields: It should not be empty and it should not include more than 8 lookup fields. If it is empty, the server will return data for all columns. If the list contains a lot of lookup fields, the query will be throttled.

    For example, we could use:

                query.ViewXml = @"<View Scope=""RecursiveAll""><Query><OrderBy Override=""TRUE""><FieldRef Name=""ID"" /></OrderBy></Query><ViewFields><FieldRef Name=""Title"" /><FieldRef Name=""Modified"" /><FieldRef Name=""Editor"" /></ViewFields><RowLimit Paged=""TRUE"">30</RowLimit></View>";

    Or if we want to retrieve items from a folder

                query.ViewXml = @"<View><Query><OrderBy Override=""TRUE""><FieldRef Name=""FileDirRef"" /><FieldRef Name=""FileLeafRef"" /></OrderBy></Query><ViewFields><FieldRef Name=""Title"" /><FieldRef Name=""Modified"" /><FieldRef Name=""Editor"" /></ViewFields><RowLimit Paged=""TRUE"">30</RowLimit></View>";

                query.FolderServerRelativeUrl = “/Lists/Announcements/Folder1”;

    Thursday, December 6, 2012 6:36 PM
  • OK Try this, I wrote the JavaScript thing a few years ago but here is a better way that I have perfected since I wrote that code:

    function initECMA_getList2(loc,loc1){

    //loc and loc1 data items being past

     titleName = loc;
     eWord = loc1;
        var clientContext = null;
        var web = null;
        SP.SOD.executeFunc('sp.js', 'SP.ClientContext', getList2);
        //ExecuteOrDelayUntilScriptLoaded(getList1, "sp.js");
    }

    function getList2() {
        try {
            var clientContext = new SP.ClientContext.get_current();
            var web = clientContext .get_web();
            var list = web.get_lists().getByTitle('Comments');
            var query = '<View Scope=\'RecursiveAll\'>'+
                            '<Query>'+
                                '<Where>'+
                                 
                                  '<Eq>'+
                                     '<FieldRef Name=\'Blog_name\'/>' +
                                     '<Value Type=\'Text\'>'+ titleName +'</Value>'+
                                  '</Eq>'+
                                 '</Where>'+
                               '<OrderBy>'+
                                 '<FieldRef Name=\'Created\' Ascending="True" />'+
                               '</OrderBy>'+
                            '</Query>'+
                           '</View>';
            var camlQuery = new SP.CamlQuery();
            camlQuery.set_viewXml(query);
            this.listItems = list.getItems(camlQuery);
            clientContext.load(listItems,'Include(Blog_name,Body,Created,ID,Created_by)');
            clientContext.executeQueryAsync(Function.createDelegate(this, this.querysuccess2), Function.createDelegate(this, this.failList2));
        }
        catch (e) {
            alert(e);
        }
    }
    function failList2(){
     alert('You must restart this page to reset login');
    }


    Richard Przybylski

    Friday, October 11, 2013 1:22 PM
  • I tried your solution, but the problem remains the same.

    The first query reads all documents in the root folder.
    This one works fine.

    <View Scope=''>
    <Query>
    <Where>
    <Eq><FieldRef Name='FSObjType'/><Value Type='Integer'>0</Value></Eq>
    </Where>
    <OrderBy Override='True'>
    <FieldRef Name='FileLeafRef' Ascending='True'/>
    </OrderyBy>
    </Query>
    <ViewFields>
    <FieldRef Name='FileLeafRef'/>
    </ViewFields>
    <RowLimit Paged='True'>1000</RowLimit>
    </View>
    
    

    The second query reads all documents in a specific subfolder.
    This one returns exception "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."

    <View Scope='RecursiveAll'>
    <Query>
    <Where>
    <And>
    <Eq><FieldRef Name='FileDirRef'/><Value Type='Text'>/Sales_9940/Deliveries</Value></Eq>
    <Eq><FieldRef Name='FSObjType'/><Value Type='Integer'>0</Value></Eq>
    </And>
    </Where>
    <OrderBy Override='TRUE'>
    <FieldRef Name='FileLeafRef' Ascending='True'/>
    </OrderyBy>
    </Query>
    <ViewFields>
    <FieldRef Name='FileLeafRef'/>
    </ViewFields>
    <RowLimit Paged='TRUE'>1000</RowLimit>
    </View>

    Has anyone been able to read from a list that contains more then 5000 items ?


    Wednesday, March 19, 2014 3:05 PM
  • My Query is very simple:

    "<View><Query><Where><Eq><FieldRef Name=\"InstallationId\" /><Value Type=\"Text\">12026046</Value></Eq></Where></Query><RowLimit>5</RowLimit></View>"

    I get the same error message but in Danish ;-)

    Just to repeat the last question above:

    Has anyone been able to read from a list that contains more then 5000 items ?

    Thanks


    Jorgen

    Tuesday, April 22, 2014 11:50 AM
  • I solved the issue this way:

    Put an Index on the column "InstallationID" and it works like a dream.


    Jorgen

    Tuesday, April 22, 2014 12:33 PM
  • Hi,

    I am trying to use OR clause in my CAML query for a list having items more than 5000. The query works fine when I replace OR with AND clause but with OR clause I am getting an error of List threshold value exceeded. I have tried all permutations and combinations - adding removing scope = recursive; Order by ID and other indexed columns, client /server object model. The columns are indexed, both the queries when run separately work fine but when combined together with OR clause gives error. The query is as posted below:

    query.ViewFields = "<FieldRef Name=\"ID\"/><FieldRef Name=\"ParkingRank\"/><FieldRef Name=\"ParkingStatus\"/>";
            query.RowLimit = 100;
            query.ViewAttributes = "Scope=\"Recursive\"";
            StringBuilder sb = new StringBuilder();
            sb.Append("<Where><Or><Eq><FieldRef Name='ParkingStatus' /><Value Type='Text'>Parked</Value></Eq><Eq><FieldRef Name='ParkingRank' /><Value Type='Text'>13</Value></Eq></Or></Where>");
            
            sb.Append("<OrderBy Override=\"TRUE\"><FieldRef Name=\"ParkingRank\"/><FieldRef Name=\"ParkingStatus\"/></OrderBy>");
          
            query.Query = sb.ToString();

    SPListItemCollection items = spList.GetItems(query);

    Can some one please confirm if there is an issue with OR clause.

    Friday, May 9, 2014 1:53 PM
  • You saved my time!! It really worked ....worked like dream

    Thank you Jogen

    Monday, May 7, 2018 1:45 PM