none
Caml query & list view threshold RRS feed

  • Question



  • I have a large list with over 100,000 items on it.  I am trying to access it with the client context, as a paged list. The following caml query works

    <View>
    <RowLimit Paged="TRUE">5</RowLimit></View>

    I can set the position with  the PagingInfo property of the list item collection 
    "Paged=TRUE&p_ID=5"  or  "Paged=TRUE&PagedPrev=TRUE"

    But when I need to filter this with a where clause 

    <View>

    <Query>

    <Where><Eq>

    <FieldRef Name="myField" /><Value Type="Text">myValue</Value></Eq></Where>

    </Query>

    <RowLimit Paged="TRUE">5</RowLimit>

    </View>

    I get the following error


    ServerException: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator

    Is there any way to address this?


    Richard


    Thursday, May 23, 2013 4:07 PM

Answers

  • Hi Richard,

    You might want to check out this document, http://msdn.microsoft.com/en-us/library/ff798465.aspx, specifically the section on how indexing affects throttling. Your query is probably throwing an exception because your query is forcing a scan of all the 100,000 rows in your list.

    Quoted from the above link:

    The list view threshold does not apply simply to the number of results returned by your query. Instead, it restricts the numbers of database rows that can be accessed in order to complete execution of the query at the row level in the content database.

    For example, suppose you are working with a list that contains 10,000 items. If you were to build a query that returns the first 100 items sorted by the ID field, the query would execute without issue because the ID column is always indexed. However, if you were to build a query that returns the first 100 items sorted by a non-indexed Title field, the query would have to scan all 10,000 rows in the content database in order to determine the sort order by title before returning the first 100 items. Because of this, the query would be throttled, and rightly so—this is a resource-intensive operation.


    Regards, Matthew
    MCPD | MCITP
    My Blog
    Please remember to click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.

    I just added a webpart to the TechNet Gallery that allows administrative users to upload, crop and format user profile photos. Check it out here: Upload and Crop User Profile Photos

    Thursday, May 23, 2013 7:02 PM

All replies

  • Hi Richard,

    Could you try this,

    var list = web.Lists["yourList"];
    var query = new SPQuery();
    query.ViewXml = "<View><Query><Where><Eq><FieldRef Name="myField" /><Value Type="Text">myValue</Value></Eq></Where></Query><RowLimit Paged='TRUE'>5</RowLimit></View>";
    var items = list.GetItems(query);

    You need to specify ViewXML property.

    Regards, Pieter
    MCPD | MCITP

    My Blog

    Please remember to click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.

    Thursday, May 23, 2013 4:24 PM
  • Hi Richard,

    You might want to check out this document, http://msdn.microsoft.com/en-us/library/ff798465.aspx, specifically the section on how indexing affects throttling. Your query is probably throwing an exception because your query is forcing a scan of all the 100,000 rows in your list.

    Quoted from the above link:

    The list view threshold does not apply simply to the number of results returned by your query. Instead, it restricts the numbers of database rows that can be accessed in order to complete execution of the query at the row level in the content database.

    For example, suppose you are working with a list that contains 10,000 items. If you were to build a query that returns the first 100 items sorted by the ID field, the query would execute without issue because the ID column is always indexed. However, if you were to build a query that returns the first 100 items sorted by a non-indexed Title field, the query would have to scan all 10,000 rows in the content database in order to determine the sort order by title before returning the first 100 items. Because of this, the query would be throttled, and rightly so—this is a resource-intensive operation.


    Regards, Matthew
    MCPD | MCITP
    My Blog
    Please remember to click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.

    I just added a webpart to the TechNet Gallery that allows administrative users to upload, crop and format user profile photos. Check it out here: Upload and Crop User Profile Photos

    Thursday, May 23, 2013 7:02 PM
  • Hi Pieter

    Thank you for the response, but I am already using the ViewXml property of the CamlQuery() 


    Richard

    Friday, May 24, 2013 8:23 AM
  • Hi Matthew. 

    Many thanks, I think this helps.   So would this help with a datetime column?   On the face of it, each column  may have a unique value, but I'd hope a date time filter would be a special enough case...


    Richard

    Friday, May 24, 2013 8:30 AM
  • Yes, (without seeing your query) it should help with a DateTime column. DateTime columns are indexable. You can see a list of all the field types that you are allowed to created indexes on here: http://msdn.microsoft.com/en-us/library/ee536168(v=office.14).aspx

    Regards, Matthew
    MCPD | MCITP
    My Blog
    Please remember to click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.

    I just added a webpart to the TechNet Gallery that allows administrative users to upload, crop and format user profile photos. Check it out here: Upload and Crop User Profile Photos

    Friday, May 24, 2013 10:54 AM
  • Hi Matthew,

    Great Post, thanks. Now I need to understand if indexed columns will still have a List View Throttle limitation? In my scenario the CAML queries are run against 2 columns which are indexed and LVT is set to 5000. 

    There is a WHERE clause within the CAML query. i.e. The first part of the clause MUST return a data set under the list view threshold.

    e.g. WHERE ColumnA = “blue” and ColumnB = “square”

    There must be less than 5000 blue items for the above query to work, even if there is only 1 blue square.

    Any explaination on why such a scenario fail/work will be helpful.

    Many thanks,

    Sabs.


    Thanks, Sabs Banerjee

    Thursday, September 5, 2013 11:56 AM
  • That is a very sad fact!!! the FIRST PRAMETER of your CAML query MUST return less than 5000 records

    Wednesday, July 23, 2014 7:20 AM
  • That is a very sad fact!!! the FIRST PRAMETER of your CAML query MUST return less than 5000 records

    I have come to this conclusion as well.

    It doesnt seem to matter even if your column is indexed,.

    Friday, November 21, 2014 5:26 PM
  • Interesting.  I read the list threshold white papers a few times and I am always needing to re-read it after finding another issue.

    I have a list now with 5000 items, but a view was created such that:

    Show all items when

    UserColumn1 = [Me] OR

    UserColumn2 = [Me] OR

    UserColumn3 = [Me] OR

    UserColumn4 = [Me]

    All of the 4 user columns are indexed, but I'm thinking that since I have OR's in they query vs ANDs that it is causing the view to trip up and display the 'only giving you recent...' yellow filter bar.

    Any thoughts?


    Herschel

    Wednesday, November 26, 2014 7:14 PM
  • If memory serves, and i have to read the article each time i do a large list, the most restrictive filter should be first. If that filter goes over the 5k limit i think you'll hit the threshold.

    Try breaking it down into components and seeing what similar, simpler views give you.

    Wednesday, November 26, 2014 7:25 PM