none
ListView Threshold not honoring indexes RRS feed

  • Question

  • I have list that has exceeded threshold.  I indexed multiple columns to use for views.  

    according to this page (http://msdn.microsoft.com/en-us/library/ff798465.aspx), if you index a column and sort by that column and tell view to limit items returned to x number, it will just return the first x records.  If I sort by ID, my view works.  If I sort by another column that I have indexed (like Modified), it does not.  How come?

    Saturday, May 10, 2014 12:40 AM

All replies

  • Hello,

    Can you try to set filer in your view? And see if sort works.

    Here is the filter

    Click Show items only when the following is true.
    select ID, is greater than or equal to, 1, click And, then select ID, is less than or equal to, 4,999.

    Thanks,

    Saturday, May 10, 2014 1:29 AM
  • All current IDs in the list are over 4,999 (we have created and deleted thousands of items in the list with automated process, so even newest item has higher id).  That said, applying boundaries on ID will make the view display, but this does not tell me why sorting by Modified is any different from sorting by ID when both are indexed columns?

    Another point of confusion: if I filter the list where a text column has a certain value (e.g., Column1 is equal to Value1) and a second filter where ID < 1500, I get error message that view exceeds threshold.  But, if I put the exact same filters but in opposite order (first filter on ID < 1500, second filter on column1 = value1) the view works.  based on the link from my first post, I can only surmise that it executes filters in sequence rather than combining them (i.e. instead of doing query in the backend that combines both criteria, it executes them in sequence and the text filter on value1--even though the column is indexed--requires the access of too many records, whereas filtering on ID first limits results before the second filter is applied).  I guess that is a heads up to any reading this - the sequence of your filters does matter.

    Monday, May 12, 2014 4:07 AM
  • Hi,

    Please check if the following has been considered:

    When creating filtered views based on column indexes, please make sure that the first column of the filter does not return more items than the List View Threshold, even if the final result of the filtered view returns less than the List View Threshold. If the first column of the filter returns more items than the List View Threshold, you can use a filter with two or more columns. When you define a filtered view that uses two or more columns, using an AND operator will usually limit the total number of items returned. But even in this case, you still need to specify as the first column in the filter the column that most likely returns the lesser amount of data.

    For more information:

    https://sladescross.wordpress.com/2011/12/18/sharepoint-2010-large-lists-and-filters-and-indexes-and-folders/

    Regards,

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com .


    Rebecca Tu
    TechNet Community Support

    Monday, May 12, 2014 9:46 AM
  • Thanks, yes, your comments and the link you provide match/confirms what I was seeing.

    This still does not answer my original question (reiterated in my last post).  In my list, with no filters at all, if I SORT by ID, the view displays successfully.  But, if I sort by Modified (also indexed column), I get error that the view exceeds the threshold.  Does anyone know/have ideas why does one work, and the other doesn't, when both are indexed columns?

    Monday, May 12, 2014 4:15 PM
  • Hmm... I cannot duplicate this behavior. On a list of mine with over 80,000 items, sorting by the indexed Modified field in descending order, with no other filters or group bys, works beautifully. Are you sure you're sorting by the correct indexed field, and that no other sorting, grouping, or filtering is taking place in your view or your query string parameters?
    Monday, May 12, 2014 7:23 PM
  • Hi,

    After indexed modified column, please create a new view only sorted by modified column and test the issue again.

    https://community.colligo.com/entries/23562490-How-can-indexing-help-to-manage-SharePoint-s-List-View-Threshold-

    Regards,


    Rebecca Tu
    TechNet Community Support

    • Marked as answer by star.warsModerator Monday, May 19, 2014 1:49 AM
    • Unmarked as answer by m_chad Tuesday, May 20, 2014 11:10 PM
    Wednesday, May 14, 2014 1:13 AM
  • This does not work.  still gives same threshold error message?


    chad miller

    Tuesday, May 20, 2014 11:04 PM
  • as mentioned before, a view sorted on ID with no filters works fine.  I created a brand new view, no filters or anything, set to sort on Modified which has been indexed (even deleted the index and re-indexed) and I still get the error.

    chad miller

    Tuesday, May 20, 2014 11:12 PM
  • Hello, I have the same issue, did you find the solution?
    Tuesday, April 25, 2017 5:54 AM
  • I think what we ended up finding regarding this is the threshold count includes items in recycle bin (even though MS documentation at the time of this ticket...may have updated after I brought to their attention in Premier ticket) said it didn't.  What's more we found that when you delete items from recycle bin there is a timer job that actually removes them from DB (i.e. you can manually clear the recycle bin (RB), but somewhere SP sees vestiges of the items even after you can no longer see them in RB).  Microsoft was vague about how often this job runs.  Said they were trying out different settings to find what worked best.  But, as I remember, they said it should not take more than a week.  Anyway, the reason the issue with the modified column...there were enough items hidden in the depths of SP (though no longer in the list itself or RB), combined with the visible items in the list, that matched those criteria that it put it over the threshold. 

    My team is now on SP Online in O365 and we recently had another instance of list defying the rules of the threshold.  A view should have only been showing 2k items but was still yielding the error.  Before MS could figure out a resolution, my team had to alter the data so the ticket was never really resolved.

    I have also since found sorting by more than one column will trigger the threshold, as will turning on total count on a column, and when filtering by multiple fields, the first filter must get the results below 5k, regardless of whether the subsequent additional filters would get the list below the threshold.  And, the way the And/or logic works on filters is bad.  It does not seem to apply order of conditions as one would like (wish there was a way to apply parenthetical grouping as in code).

    All that to say, I hate the 5k threshold and wish MS would improve the way it is applied.  Or at minimum, that they would fix the cleanup job (and/or give admins visibility/control over when it runs) that removes data from DB once deleted from Recycle Bin, as I still believe that causes issues.  Anyway, hope some of that helps. 


    chad miller

    Tuesday, April 25, 2017 2:55 PM